Compare two tables and only show unique values from specific column

[JayWeye]

Hello all, 

In essense, I have 2 tables. 

One table has multiple people running a report and that is being fed into a sharepoint list

the column I want has a specific unique code that is assigned to them i.e 12345. 

Some people run this multiple times so there could be occasions there is a duplicate in the first table. 

Table 1 Example

Code

1233

9879

1233

5673

The second table is a directory of every single unique code and a few other important details that need to be shown in other columns. (No duplicates)

What I want to do, is take the data from table 1, compare it to table 2 and then show on a pie chart the codes that aren't shown in table 1 so that we have a list of people that haven't run a report. 

The 2 tables are currently on a one to many relationship (Table 1 many - Table 2 one)

Any help would be massively appreciated. 

[bolfri]

Table 1

Code

1233

9879

1233

5673

Table 2

CodeName

1233	Joe
9879	Steve
5673	Bolfri
6666	Random
9876	Beth

Relationship

DAX

Number of reports = COUNTROWS('Table 1')

People without reports = 
    COUNTROWS(FILTER('Table 2',ISBLANK([Number of reports])))

[JayWeye]

hello, thank you for your response. I've tried this, again like the comment above I'm not sure if I've done it right but it displays the answer as 15k. 

As both tables have multiple columns and sometimes in table 1 there are duplicates of people running it monthly, does it look at unique codes or totals all of the rows together?

[bolfri]

Number of reports = DISTINCTCOUNT('Table 1'[Code])

Change this measure. Should work fine.

[vicky_ · Accepted solution]

Could you try something like: 

not in table = COUNTROWS(EXCEPT(VALUES('Table (2)'[Unique]), VALUES('Table'[Not Unique]))) 

and then use that in the pie chart values?

[JayWeye]

Please ignore me, I have tried this again and it is working. Thank you so much! 

[JayWeye]

hello, thank you for your response. I have tried this but the result is around 21k when it should be around 150. 

I may not be doing it right myself, but it doesn't display the correct number 🙂