MDX Query/Expression not working

Question

I am new to MDX expressions and queries and I am currently looking for something like this -

I have two dates 'Date1' & 'Date2' in Fact Table as Foreign Keys to DATE dimension. I need to do a count of rows where Date1 < Date 2, keeping in account that I don't want to count NULLS. I wrote an expression something like this -

WITH MEMBER [Measures].[RecordCount] AS
COUNT(FILTER([Measures].[RecordCount], IIF([Date1].[Date] <= [Date2].[Date],0,1)=1))
SELECT [Measures].[RecordCount] ON 0
FROM [MYCUBE]

The above queries runs fine, but the count turns out to be incorrect. I created 7 rows in my fact table where Date1 is less than Date2, but still I receive the count as 0.

Any help is appreciated. (any reference sites would be good too for future)

Thanks, Vineet [email protected]

Darren Gosbell Darren Gosbell · Accepted Answer · 2009-02-09T22:59:53

You can't really do this easily in MDX, the [RecordCount] measure will be aggregated up before you do the comparison of the dates, so you will never get a valid value.

You would be better to create a named calculation in your DSV that has something like

CASE WHEN Date1 < Date2 THEN 1 ELSE NULL END

and then create a measure in your cubes that sums up this new column.

MDX Query/Expression not working

1 Answers