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. 

     

Greg_Deckler's avatar
Greg_Deckler
Community Champion
6 years ago

Well, if these are actually tables then what you are trying to do cannot be done because tables only update upon data load/refresh.

 

You would need a measure.

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
    Community 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
        Community 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.