MDX: Query if row exists in dimension

Question

I have created a SSAS Tabular model cube which this connects too. The cube contains Measures: - CountA, SumB, CountC, SumD, CountE Dimensions: - TblLabelDefinition(ID,Definition,Type)
- TblLabels(ID,Type,reference,date,sold,leftover,A,B,C,D,E)
- References(ID,reference,date)

Below is a query I am working on:

SELECT 
NON EMPTY 
    { 
     [Measures].[CountA], 
     [Measures].[SumB], 
     [Measures].[CountC], 
     [Measures].[SumD], 
     [Measures].[CountE] 
    } 
ON COLUMNS, 
NON EMPTY 
    { 
     (
      [TblLabelDefinition].[Definition].[Definition].ALLMEMBERS * 
      [TblLabels].[Type].[Type].ALLMEMBERS 
     ) 
    }
DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS 
FROM 
    ( 
        SELECT ( -{ [TblLabels].[sold].&[1] } ) ON COLUMNS 
        FROM 
            ( SELECT ( { [TblLabels].[leftover].&[0] } ) ON COLUMNS 
                FROM [Model]
            )
    ) 
WHERE 
        ( [TblLabels].[leftover].&[0] ) 
        AND 
        (
         EXISTS([TblLabels].[reference], [References].[reference])
        )   
CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, 
FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

I am trying to check if reference in the TblLabels table exists in References table.

This is the error I get: The And function expects a string or numeric expression for the 2 argument. A tuple set expression was used.

SouravA SouravA · Accepted Answer · 2015-12-08T10:44:09

Try the below to get rid of the syntactic error:

SELECT 
NON EMPTY 
    { 
     [Measures].[CountA], 
     [Measures].[SumB], 
     [Measures].[CountC], 
     [Measures].[SumD], 
     [Measures].[CountE] 
    } 
ON COLUMNS, 
NON EMPTY 
    { 
     (
      [TblLabelDefinition].[Definition].[Definition].ALLMEMBERS * 
      [TblLabels].[Type].[Type].ALLMEMBERS 
     ) 
    }
DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS 
FROM 
    ( 
        SELECT ( -{ [TblLabels].[sold].&[1] } ) ON COLUMNS 
        FROM 
            ( SELECT ( { [TblLabels].[leftover].&[0] } ) ON COLUMNS 
                FROM [Model]
            )
    ) 
WHERE 
        (
         [TblLabels].[leftover].&[0]
         , 
        (
         EXISTS
              (
               [TblLabels].[reference], 
               [References].[reference]
              )
        ))   
CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, 
FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

As a side note:

     EXISTS
          (
           [TblLabels].[reference], 
           [References].[reference]
          )

yields a set of those references that exists in the table TblLabels for one or more references in the table References. If these tables are joined on the 'reference' column then the above EXISTS construct would return only those references that are present in both References and TblLabels. Do check if that is what you needed in the first place.

MDX: Query if row exists in dimension

1 Answers