Create Table from difference between two columns in two tables

[mbailey]

Hi,

My data sources are two tables that each have a column for a PC's name (Table1.HostName, Table2.DeviceName).

I've created measures like the one below to count the differences between the two, which works fine. Now, I want to be able to display in a grid the actual devices missing, and can't figure out how to do it. Can anyone help?

Count of Table1 Missing from Table2 =

VAR SelT1 = VALUES('Table1'[Device Name])

VAR SelT2 = VALUES('Table2'[Hostname])

RETURN

COUNTROWS(EXCEPT(SelT1, SelT2))

Table1		Table2
HostName		DeviceName
PC-1		PC-1
PC-2		PC-3
PC-3		PC-4
PC-4		PC-5
PC-5		PC-6
PC-6		PC-8
PC-7		PC-9
PC-8		PC-10
PC-9		PC-11
PC-10		PC-12
PC-12

[Anonymous · Accepted solution]

Hi @mbailey ,

The result returned by a measure can only be a scalar, it cannot return table results.

You may consider using merge queries in the PowerQuery editor.

Or create a calculated table.

Table = EXCEPT(DISTINCT('Table1'[HostName]),DISTINCT('Table2'[DeviceName]))

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum