Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
108 | |
105 | |
88 | |
74 | |
66 |
User | Count |
---|---|
124 | |
112 | |
98 | |
81 | |
72 |