Multiple statement check in if statement in mdx

Question

i made the calculated measure in ssas multidimensional cube i want to ask that where is wrong in code

i want to check two dimension values

if DIM SALES ALL CHANNELS].[PO NUMBER] is not null And DIM_CATAGEORY.VALUE = 'soos'

THEN gross_excluding_vat

ELSE

gross_amount

i write this but its showing error and null values

IIf([DIM ITEM].[TAX CATEGORY CHANNELS].CURRENTMEMBER IS [DIM ITEM].[TAX CATEGORY CHANNELS].&[SA OOS], IIF([DIM SALES ALL CHANNELS].[PO NUMBER].[],[Measures].[GROSS EXCLUDING VAT],[Measures].[GROSS AMOUNT])

,[Measures].[GROSS AMOUNT])

MoazRub MoazRub · Accepted Answer · 2019-09-10T20:17:28

The problem in your expersion is in the following part.

IIF([DIM SALES ALL CHANNELS].[PO NUMBER].[] The problem is that you are providing it a member where it is looking for a logical expression.

Try the expression below it should work. It will return [Measures].[GROSS EXCLUDING VAT] whenever Dim Item is [SA OOS] and anyvalue except for the default value of [DIM ITEM].[TAX CATEGORY CHANNELS] is selected.

IIf([DIM ITEM].[TAX CATEGORY CHANNELS].CURRENTMEMBER IS [DIM ITEM].[TAX CATEGORY CHANNELS].&[SA OOS], 
IIF(not [DIM ITEM].[TAX CATEGORY CHANNELS].CURRENTMEMBER IS  [DIM SALES ALL CHANNELS].[PO NUMBER].defaultmember,[Measures].[GROSS EXCLUDING VAT],[Measures].[GROSS AMOUNT])
,[Measures].[GROSS AMOUNT])

Multiple statement check in if statement in mdx

1 Answers