MDX Query/Expression not working

Question

Hi, 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]

Answer 1

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.