I need a quick help on the below mdx query

Question

I want to filter the data on the measure and dimension at a time

case 
when [measure].[frequency] >3 and [poa].[segment].&A then 'red'
when [measure].[frequency] <3 and [poa].[segment].&A then 'yellow'
when [measure].[frequency] =3 and [poa].[segment].&A then 'Green'
else 'NA' end

these the script i have written in the calculated member .. but it is not running successfully.Kindly help us

Answer 1

Do you need to put a currentMember comparison in the case?

I guess this would work ok?

case 
when [measure].[frequency] >3 then 'red'
when [measure].[frequency] <3 then 'yellow'
when [measure].[frequency] =3 then 'Green'
else 'NA' 
end

Although do you have to use 'NA'? Can you not use null in this situation?

case 
when [measure].[frequency] >3 then 'red'
when [measure].[frequency] <3 then 'yellow'
when [measure].[frequency] =3 then 'Green'
else NULL 
end

The other section of you calc looks like it needs to compare [poa].[segment].&A against something using the IS operator like this:

([poa].CURRENTMEMBER IS [poa].[segment].&A)

So adding this into the case statement:

CASE
WHEN [measure].[frequency] >3 
        AND ([poa].CURRENTMEMBER IS [poa].[segment].&A) THEN 'red'
WHEN [measure].[frequency] <3 
        AND ([poa].CURRENTMEMBER IS [poa].[segment].&A) THEN 'yellow'
WHEN [measure].[frequency] =3 
        AND ([poa].CURRENTMEMBER IS [poa].[segment].&A) THEN 'Green'
ELSE NULL 
END