none
DAX Dimension Composite Key Syntax Question

    Question

  • Hello,

    Does anyone know how to correctly reference a dimension in DAX?  See attached screenshot.  I'm trying to reference Email attribute within the Person dimension. 

    Error Message

    "Column [Email] is part of composite key, but not all columns of the composite key are included in the expression or its dependent expression."

    Happy New Year to you!

    Tuesday, December 31, 2013 3:26 AM

Answers

  • ok, you may try this then:

    ...
    CALCULATETABLE(
        ....,
        'Person'[Email] = "TSTUBBLE",
        'Person'[Email.Key0] = "TSTUBBLE"
    )
    ...

    i guess in your multidimensional model the key-column and the name-column were set and thats why you need to use this syntax to reference both of them

    hth,
    gerhard


    Gerhard Brueckl
    blogging @ http://blog.gbrueckl.at
    working @ http://www.pmOne.com

    Monday, February 03, 2014 9:43 AM

All replies

  • the error occurs because, as it says, [Email] is part of a compound key and you need to filter on all columns of the compound key

    what is important to mention here is the fact that also a name-column in multidimensional is reffered to as a compound key in DAXMD

    when you run the following query:

    you will realize that you get (at least) two columns for your original [Email]-attribute

    one of them is suffixed as [Email.Key0] - this refers to what you defined as key-column of your multidimensional attribute. the other column is [Email] without any suffix. 
    When you use CALCULATETABLE it expects the columns and values in that column to be unique which is not necessarily the case if you only filter by [Email] as two persons may have the same email-address (theoretically)
    so you need to filter on [Email.Key0] if you want to use CALCULATETABLE

    an other option is to use FILTER instead of CALCULATETABLE which will work in both scenarios as FILTER does not rely on unique values in a column, though FILTER might be slower

    hth,
    gerhard


    - www.pmOne.com -

    Thursday, January 02, 2014 12:53 PM
  • Thanks Gerhard. 

    Do I add another line right below 'Person'[Email]="TStubble" as such: 'Person'[Email.Key0]="TStubble"?

    BTW, did you miss pasting the query?

    Also, as you can see from my query, I already have a FILTER condition on NOT IsBlank([Actual Person]), so it does not allow me to add another condition.  Is it true that only one condition is allowed under FILTER function?  If so, why is there only one condition allowed?

    Your help is appreciated!

    Friday, January 03, 2014 7:56 AM
  • ya, seems like i forgot to paste the query :)

    here it is:
    EVALUATE('Person')

    just to check what columns exist in your table and which ones you need to filter

    you can combine several conditions in your FILTER -function
    you can either use AND() or OR() function or use "&&" for AND or "||" for OR

    hth,
    gerhard


    - www.pmOne.com -

    Friday, January 03, 2014 8:56 AM
  • Hello Gerhard,

    I don't think your expression helped find the key needed for my query.
    I see bunch of columns when I enter your expression.  Not sure which columns I need to add (hopefully not all of them) below my existing email column under Person dimension: 'Person'[Email]="tstubble"

    See result below when I entered EVALUATE('Person'):

    Monday, January 20, 2014 7:44 PM
  • can you list all available columns?

    the ones we are looking for would be like with "Persion[Email*"


    - www.pmOne.com -

    Tuesday, January 21, 2014 6:54 PM
  • Is nearly impossible to list all columns.  There's at least 50 if not more related to Person dimension.

    I found another column related to [Email].  See image below, so I added the following line right after 'Person'[Email]="TYHILL", but still received the same error.

    'Person'[Email.UniqueName]="TYHILL"

    There's this Key0 field but is related to Personnel Number.  Do I also need Personnel Number?  But I wouldn't know how to filter since I wouldn't know each person's personnel number.

    Tuesday, January 21, 2014 8:59 PM
  • ok, lets try it the other way round then

    how is your Email-attribut set up in the multidimensional cube?
    key-column(s)? name-column? value-column?


    - www.pmOne.com -

    Thursday, January 23, 2014 10:53 AM
  • I have no idea about the data model for I am simply a consumer of the cube.  Is there commands such as EVALUATE('Person') to decipher more about the model, especially dimension key? 

    All I am trying to do is pull Person's Email attribute through DAX.  I didn't know is quite complex.

    Thursday, January 23, 2014 6:51 PM
  • Is there limitation with DAX Composite Key to SSAS Tabular Cube?
    Friday, January 24, 2014 4:00 PM
  • its not directly a limitation but according to microsoft its "by design":
    https://connect.microsoft.com/SQLServer/feedback/details/775561/inconsistent-behavior-for-dax-queries-if-a-namecolumn-is-specified

    This behavior is the current design. Due to the current 
    DAX semantics and for the ability for clients to query the
    key column, we expose the metadata via CSDL-BI. Clients 
    are expected to interpret the CSDL-BI metadata and form
    the appropriate DAX query for the right results. 

    for your initial problem - could you just drag-and-drop one of the members of your EMail-attribute to the MDX window and post its unique name here? this should also reveal some more insights
    please also post the name of that member



    Gerhard Brueckl
    blogging @ http://blog.gbrueckl.at
    working @ http://www.pmOne.com

    Monday, January 27, 2014 9:14 PM
  • Your bug filing helps to understand some potential hassle.  However, I'm not sure how to find unique key for a given dimension.  I'm guessing the unique name might be personnel number.

    I'm not sure what you mean by post unique name.  Here's screenshot of drag and drop Email attribute into MDX.  Still trying to figure out the syntax for finding unique key and DAX syntax to simply pull Email.

    Tuesday, January 28, 2014 12:04 AM
  • well, thats the designer view, you will not see any MDX 

    to switch to MDX view you need to use the button in your ribbon at the very right that looks like a triangle:

    


    Gerhard Brueckl
    blogging @ http://blog.gbrueckl.at
    working @ http://www.pmOne.com

    Tuesday, January 28, 2014 8:05 AM
  • Here's the MDX based on the screenshot above.  Let me know what other syntax you want me to provide to obtain the unique key for Person dimension.  Thanks!

    SELECT NON EMPTY { [Measures].[Actual People] } ON COLUMNS, NON EMPTY { ([Person].[Email].[Email].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM ( SELECT ( { [Person].[Email].&[A-AZMI] } ) ON COLUMNS FROM [Current Headcount]) CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

    Tuesday, January 28, 2014 6:58 PM
  • ok, you may try this then:

    ...
    CALCULATETABLE(
        ....,
        'Person'[Email] = "TSTUBBLE",
        'Person'[Email.Key0] = "TSTUBBLE"
    )
    ...

    i guess in your multidimensional model the key-column and the name-column were set and thats why you need to use this syntax to reference both of them

    hth,
    gerhard


    Gerhard Brueckl
    blogging @ http://blog.gbrueckl.at
    working @ http://www.pmOne.com

    Monday, February 03, 2014 9:43 AM