Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

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

Need Help
  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    7 years ago

    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
    )

4 Replies

  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    Community Champion
    7 years ago

    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's avatar
      Anonymous
      Not applicable
      7 years ago

      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

      • Zubair_Muhammad's avatar
        Zubair_Muhammad
        Community Champion
        7 years ago

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