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