cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

Comparing 3 columns in 3 different tables

Hi,

 

I have 3 tables and each of them has a "Name" column titled Name1, Name2 and Name3, respectively. All three columns contain device numbers. 

 

I'm trying to find the device numbers that do not appear in all 3 columns, i.e. those that only appear in one or two of the columns. 

 

I can put the three columns in a table visual in which case I get something like this:

 

Name1Name2Name3
Device1Device1 
Device2  
Device3Device3Device3
Device4 Device4
  Device5
Device6Device6Device6
 Device7 
 Device8Device8
Device9Device9Device9
Device10  

 

I'd like to filter out the devices that appear in all 3 columns. 

 

Do you guys have any idea how to do this?

 

Thanks,

Ginny

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Comparing 3 columns in 3 different tables

@Anonymous 

 

Try this calculated table>>from the modelling tab>>New Table

 

Calc Table =
VAR DevicesInAllTables =
    INTERSECT (
        INTERSECT ( VALUES ( Table1[Name1] ), VALUES ( Table2[Name2] ) ),
        VALUES ( Table3[Name3] )
    )
RETURN
    EXCEPT (
        DISTINCT (
            UNION (
                VALUES ( Table1[Name1] ),
                VALUES ( Table2[Name2] ),
                VALUES ( Table3[Name3] )
            )
        ),
        DevicesInAllTables
    )

View solution in original post

4 REPLIES 4
Super User
Super User

Re: Comparing 3 columns in 3 different tables

@Anonymous 

 

Try this calculated table>>from the modelling tab>>New Table

 

Calc Table =
VAR DevicesInAllTables =
    INTERSECT (
        INTERSECT ( VALUES ( Table1[Name1] ), VALUES ( Table2[Name2] ) ),
        VALUES ( Table3[Name3] )
    )
RETURN
    EXCEPT (
        DISTINCT (
            UNION (
                VALUES ( Table1[Name1] ),
                VALUES ( Table2[Name2] ),
                VALUES ( Table3[Name3] )
            )
        ),
        DevicesInAllTables
    )

View solution in original post

Anonymous
Not applicable

Re: Comparing 3 columns in 3 different tables

Thank you so much, this worked perfectly.

 

I now have a list of all the device numbers that only appear in one or two of the columns. Is there a way to also show which columns are missing these device numbers? So using my pervious example, have something like:

 

Device #Missing from
Device1Name3
Device2Name2, Name3
Device4Name2
Device5Name1, Name2
Device7Name1, Name3
Device8Name1
Device10Name2, Name3

 

If you have any ideas if/how this could be possible, please let me know 🙂

 

Many thanks,

Ginny

Super User
Super User

Re: Comparing 3 columns in 3 different tables

@Anonymous 

 

you can use soemthing like this.

Please see attached file as well

 

 

Column =
VAR temp =
    {
        IF ( ISEMPTY ( FILTER ( Table1, Table1[Name1] = CalcTable[Name1] ) ), "Name1" ),
        IF ( ISEMPTY ( FILTER ( Table2, Table2[Name2] = CalcTable[Name1] ) ), "Name2" ),
        IF ( ISEMPTY ( FILTER ( Table3, Table3[Name3] = CalcTable[Name1] ) ), "Name3" )
    }
RETURN
    CONCATENATEX ( FILTER ( temp, [Value] <> BLANK () ), [Value], ",", [Value] )

calcu.png

Anonymous
Not applicable

Re: Comparing 3 columns in 3 different tables

Amazing, you're a star. Thank you.

Helpful resources

Announcements
October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 165 members 1,571 guests
Please welcome our newest community members: