CODEWITHSUNDEEP
mdx
Vineet
Vineet

Reputation: 81

MDX Query/Expression not working

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]

Upvotes: 2

Views: 774

Answers (1)

Darren Gosbell
Darren Gosbell

Reputation: 1940

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.

Upvotes: 2

Related Questions