Forum Discussion
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 ...
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 )
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
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] )
Amazing, you're a star. Thank you.