Search code examples
ssasmdx

MDX query for fact count

I want MDX(Query) expression for namedset or calculated measures for below

Tsql:

When [product].[Name] in (car1,car2,car3,car4,car5) Then [Measure].[Factcount] =0 ELSE [Measure].[Factcount] =0

I tried below MDX

IIF ({[product].[Name].&[car1], [product].[Name].&[car2], [product].[Name].&[car3],[product].[Name].&[car4], [product].[Name].&[car5]},[Measure].[Factcount] =0,[Measure].[Factcount] =1)

Solution

  • Take a look at the sample below 

    with member measures.test 
as 
case 
when [Product].[Subcategory].currentmember is [Product].[Subcategory].&[18] then 0
when [Product].[Subcategory].currentmember is [Product].[Subcategory].&[2] then 0
when [Product].[Subcategory].currentmember is [Product].[Subcategory].&[22] then 0
when [Product].[Subcategory].currentmember is [Product].[Subcategory].&[17] then 0
when [Product].[Subcategory].currentmember is [Product].[Subcategory].&[11] then 0
else 1 
end 
select measures.test 
on columns,
[Product].[Subcategory].[Subcategory]
on rows 
from 
[Adventure Works]