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

Why is it not working? Do you get an error or incorrect results? Provide a screenshot of the wrong results and your desired results. — GregGalloway, Mar 06 '16 at 02:09
This is not a help desk where you can request us to answer ASAP. — Martin Smith, Mar 06 '16 at 15:31
@AdiT please install OLAP PivotTable Extensions then right click on the #VALUE! cell and get a better error message. http://olappivottableextend.codeplex.com/wikipage?title=View%20Error%20Message&referringTitle=Home — GregGalloway, Mar 09 '16 at 04:38

score 0 · Answer 1 · answered Mar 06 '16 at 09:22

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

I need a quick help on the below mdx query

1 Answers1