Comparing 3 columns in 3 different tables

[Anonymous]

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:

Name1	Name2	Name3
Device1	Device1
Device2
Device3	Device3	Device3
Device4		Device4
		Device5
Device6	Device6	Device6
	Device7
	Device8	Device8
Device9	Device9	Device9
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

[Zubair_Muhammad · Accepted solution]

@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
    )

[Anonymous]

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
Device1	Name3
Device2	Name2, Name3
Device4	Name2
Device5	Name1, Name2
Device7	Name1, Name3
Device8	Name1
Device10	Name2, Name3

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

Many thanks,

Ginny

[Zubair_Muhammad]

@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] )

[Anonymous]

Amazing, you're a star. Thank you.