WHERE clause in MDX for Dynamic Management Views

Question

I am trying to query the metadata from a tabular cube using DMVs. I was able to get it to work without a where clause, but I can't seem to get a where clause to work. Any Advice?

Here is the code that works:

SELECT 
    [MEASURE_CAPTION]           AS [Measure]
    ,[MEASURE_IS_VISIBLE]       AS [Visable]
    ,[DESCRIPTION]              AS [Description]
    ,[MEASURE_DISPLAY_FOLDER]   AS [Display Folder]
    ,[EXPRESSION]               AS [Calculation]
FROM $SYSTEM.MDSCHEMA_MEASURES

The WHERE clauses that I have tried are:

WHERE ([MEASURE_IS_VISIBLE].[members].[true])

I get the following error:

The dot expression is not allowed in the context at line 9, column 1.

Also:

WHERE [MEASURE_IS_VISIBLE] = TRUE

I get the following error:

Error: A Boolean expression is not allowed in the context at line 9, column 7.

I have tried many veriations on these themes, but always get the same result. I am not at all familiar with how MDX works, so any assistance would be appreciated.

Answer 1

MEASURE_IS_VISIBLE is a boolean column and must be filtered as such. The following query filters the results to only those with a MEASURE_IS_VISIBLE value of true. Change this to WHERE NOT MEASURE_IS_VISIBLE for rows where this is false. You can see the datatypes for this DMV from the documentation here.

SELECT 
    [MEASURE_CAPTION]           AS [Measure]
    ,[MEASURE_IS_VISIBLE]       AS [Visable]
    ,[DESCRIPTION]              AS [Description]
    ,[MEASURE_DISPLAY_FOLDER]   AS [Display Folder]
    ,[EXPRESSION]               AS [Calculation]
FROM $SYSTEM.MDSCHEMA_MEASURES 
WHERE MEASURE_IS_VISIBLE