Forum Discussion

mbailey's avatar
mbailey
Helper II
3 years ago
Solved

Create Table from difference between two columns in two tables

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   
Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    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

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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