Help with calculation - count IDs except IDs in another table

[arb1782]

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

[Anonymous]

Try this,

Measure  = COUNTROWS(Table1) - DISTINCTCOUNT(Table2[ID])

[arb1782]

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

ID	Category
001	A
002	A
003	B
004	A
005	C

Table 2

ID

001

001

003

004

005

[Yggdrasill]

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]

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?

[AlB]

Hi @arb1782 

What do you mean it doesn't work? It sure does. See it at play in the attahced file

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 

[AlB]

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