Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Slicing two tables

Hello, I’m relatively new to DAX and struggling with the following, so any help would be greatly appreciated. Apologies, if this has been asnwered already, but I couldn't find anything similar.   I...
Need Help
  • v-eachen-msft's avatar
    v-eachen-msft
    6 years ago

    Hi Anonymous ,

     

    You could try SELECTEDVALUE() function.

    Count=
CALCULATE (
    COUNTA ( 'Table1'[Combination] ),
    FILTER (
        'Table1',
        SEARCH ( SELECTEDVALUE ( 'Table2'[ID] ), 'Table1'[Combination],, 0 )
    )
)

    But I think the count of c should be 2 when you select x. 

     

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Okay, good to know, thanks!

 

However, when I tried to create a measure, I got a mistake becasue it can't find an aggregation for Table2[ID] since it's in text form.

Does this make sense? Thanks again!

Greg_Deckler's avatar
Greg_Deckler
Icon for Community Champion rankCommunity Champion
6 years ago
Generally you just wrap it with MAX or MIN depending on your preference. Doesn't matter what the aggregation is when it is filtered to one. Anonymous
  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Thank you again, and please forgive my insistence! I appreciate your patience

     

    When I aggregate by MAX (or MIN) I get the same results for each ID level and therefore lose valuable information:

    IDCount
    a2
    b2
    c2
    d2

     

    Instead of:

    IDCount
    a2
    b1
    c1
    d1

     

    Greg_Deckler 

    • v-eachen-msft's avatar
      v-eachen-msft
      Icon for Community Support rankCommunity Support
      6 years ago

      Hi Anonymous ,

       

      You could try SELECTEDVALUE() function.

      Count=
CALCULATE (
    COUNTA ( 'Table1'[Combination] ),
    FILTER (
        'Table1',
        SEARCH ( SELECTEDVALUE ( 'Table2'[ID] ), 'Table1'[Combination],, 0 )
    )
)

      But I think the count of c should be 2 when you select x. 

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        Thanks v-eachen-msft , that works!

        And yes, you're right, was indeed supposed to be 2 in that example.