Wednesday, February 2, 2011

Filter and Count in MDX

Filter and Count in MDX
If you have database table as shown and you requirement is to filter value out before you count the row. In my example, notice the highlighted row, I got 0 score for those 2 rows and I don't want such row included when I either create a calculated member in the cube or write report using MDX. In this example I need to get sum of the programs only if score > 0. This is how you the filter condition with count will look -
MEMBER [Measures].[Program_Count] AS
count (filter([TOP PROGRAMS].[PROGRAM ID].[PROGRAM ID], [Measures].[PROG SCORE]>0))

No comments:

Post a Comment