Report Portal

MDX-How do you compare dimension level name to specific value

Q: How do you compare dimension level name to specific value?

A: Best way to compare if specific dimension is at certain level is by using 'IS' operator:
Example:

WITH MEMBER [Measures].[TimeName] AS
IIF([Date].[Calendar].Level IS [Date].[Calendar].[Calendar Quarter],'Qtr','Not Qtr')
SELECT [Measures].[TimeName] ON 0
FROM [Sales Summary]
WHERE ([Date].[Calendar].[Calendar Quarter].&[2004]&[3])

You also have an option to use .Name function, but this is not recommended:

WITH MEMBER [Measures].[TimeName] AS IIF([Date].[Calendar].Level.Name = 'Calendar Quarter','Qtr','Not Qtr')
SELECT [Measures].[TimeName] ON 0
FROM [Sales Summary]
WHERE ([Date].[Calendar].[Calendar Quarter].&[2004]&[3])

You could also check level number using function .Ordinal

Also you can find more information in Mosha Pasumansky blog here.

Tags: mdx, faq

 

2007-2015 VidasSoft Systems Inc.