Forum Discussion

arb1782's avatar
arb1782
Helper III
5 years ago

Help with calculation - count IDs except IDs in another table

Hi

 

I have data like this and I want to count the number of IDs in table 1 minus those that are mentioned at least once in table 2. Could anyone help me with the right DAX? With the example data below I would be looking for answer of 2 (5 - 3).

 

Table 1 - each row is a unique ID

ID

A001

A002
A003
A004
A005

 

Table 2 - each row can contain non-unique ID from table 1

 

ID
A001
A001
A003
A004
A004
A001

 

Thanks

arb

Need Help

6 Replies

  • AlB's avatar
    AlB
    Community Champion
    5 years ago

    Hi arb1782 

    Place this measure in a card visual

     

    Measure =
COUNTROWS(EXCEPT(DISTINCT(Table1[ID]), DISTINCT(Table2[ID])))

     

    Please mark the question solved when done and consider giving kudos if posts are helpful.

    Contact me privately for support with any larger-scale BI needs, tutoring, etc.

    Cheers 

     

  • Yggdrasill's avatar
    Yggdrasill
    Responsive Resident
    5 years ago

    What you're asking for is called Left anti join and you can easily do this in Power Query. However, in DAX, try the DAX function EXCEPT()

    Create a new table:

    New Table = EXCEPT(table1, table2)

    You could probably do this without having to create another table though

    • arb1782's avatar
      arb1782
      Helper III
      5 years ago

      Hi

       

      Thanks for the suggestions so far. I'm afraid that measure doesn't work and creating a table doesn't quite solve my issue as I want to display a single number on a scorecard.

       

      Any other ideas?

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Try this,

    Measure  = COUNTROWS(Table1) - DISTINCTCOUNT(Table2[ID])
    • arb1782's avatar
      arb1782
      Helper III
      5 years ago

      HI Anonymous AlB Yggdrasill 

       

      Thanks for the suggestions so far.

       

      I realise I should have been a bit clearer about what I am looking to do - apologies. I need to subtract the unique IDs that appear in table 2 if they appear in a subset from table 1.

       

      So with the example data below, I'd be looking for the answer of 1 (3 from table 1 minus 2 from table 2)

       

      Table 1

      IDCategory 
      001A 
      002A 
      003B 
      004A 
      005C 

       

      Table 2

      ID
      001
      001
      003
      004
      005