MDX: filtering zero values out

Question

I have a dataset that has a lot of 0 values for the measure so I would like to filter them out in my query. However I don't seem to be able to do it.

It either doesn't work or I crash the query. I have tried using Having and also Filter but neither works. This last one crashes the query, wondering if anyone can see where I am going wrong?

SELECT NON EMPTY 

{ [Measures].[Inv Avail] }  HAVING  [Measures].[Inv Avail] <> 0  ON COLUMNS, 

NON EMPTY { Filter(
(
[Date].[Day].[Day].ALLMEMBERS * 
[Product].[SKU ID].[SKU ID].ALLMEMBERS * 
[Product].[Desc].[Desc].ALLMEMBERS * 

{[Warehouse].[Warehouse Code].[4552]}) } 

,[Measures].[Inv Avail] > 0)

DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS 

FROM (     
SELECT ( {       
StrToMember('[Date].[Week].&[48]')
 } ) ON COLUMNS 
FROM [Warehouse Inventory])

MoazRub · Answer 1 · 2018-11-30T18:07:11.650

This should work. Your row axis had brackets problems

SELECT NON EMPTY 

[Measures].[Inv Avail]   ON COLUMNS, 

NON EMPTY { 
Filter(
(
[Date].[Day].[Day].ALLMEMBERS * 
[Product].[SKU ID].[SKU ID].ALLMEMBERS * 
[Product].[Desc].[Desc].ALLMEMBERS * 
[Warehouse].[Warehouse Code].[4552]
)

,[Measures].[Inv Avail] > 0)
}

DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS 

FROM (     
SELECT ( {  StrToMember('[Date].[Week].&[48]') } ) ON COLUMNS 
FROM [Warehouse Inventory]
)

MDX: filtering zero values out

1 Answers1