I have a calculated Member with this formula:
CASE
WHEN SUM([Time].[Year].currentmember ,[Measures].[Amount]) > 0
THEN 0
ELSE 1
END
This calculated member works as I wish but when I pull a dimension member like name even after passing the filter for year, when I pass this calcuated member it pulls data for all years.
For instance if I have data from 2000-2010 then when I pass filter as 2007, this member pulls data for all years showing blank values. Once I remove this calculated member then it works fine, it shows data only for 2007. I know am messing up in MDX, please help me thanks
I think you pretty much have the correct answer but you need to swap the 0
for null
instead:
CASE
WHEN SUM([Time].[Year].currentmember ,[Measures].[Amount]) > 0 THEN 1
WHEN SUM([Time].[Year].currentmember ,[Measures].[Amount]) < 0 THEN 0
ELSE
NULL
END