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.
I have two separate tables that I'm trying to compare and determine what the differences are. Table A has a list of all the plants currently available. Table B has a list of the plants currently in service. I'd like to know the plants that are currently not in service. An example is listed below.
Table A | Table B | Expected Result |
Plant 1 | Plant 3 | Plant 1 |
Plant 2 | Plant 6 | Plant 2 |
Plant 3 | Plant 7 | Plant 4 |
Plant 4 | Plant 5 | |
Plant 5 | ||
Plant 6 | ||
Plant 7 |
Is it possible to create a dynamic list of the plants not in service?
Solved! Go to Solution.
A: If you want just a visual to show this:
Assuming that are related
Create a Measure: InTableB = COUNTROWS(Table2)
Use a Visual
Values - Plant of Table A
Visual Filter : InTableB is Blank
B: Want a New Table:
Create a new table in DAX. (Modeling--New Table)
Assuming that are only 1 column on each table:
NewTable = EXCEPT('Table1';Table2)
And if you have 2 or more columns:
NewTable = EXCEPT(VALUES(Table1[Plant]),VALUES(Table2[Plant]))
In DAX the formula would be this
New Table = EXCEPT('Table A','Table B')
In the query-editor, the M-formula would look like this:
Difference = List.Difference( { TableA[ColumnName], TableB[ColumnName] } )
😉
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
A: If you want just a visual to show this:
Assuming that are related
Create a Measure: InTableB = COUNTROWS(Table2)
Use a Visual
Values - Plant of Table A
Visual Filter : InTableB is Blank
B: Want a New Table:
Create a new table in DAX. (Modeling--New Table)
Assuming that are only 1 column on each table:
NewTable = EXCEPT('Table1';Table2)
And if you have 2 or more columns:
NewTable = EXCEPT(VALUES(Table1[Plant]),VALUES(Table2[Plant]))
Thanks to everyone that provided possibilities to solve my problem.
Vvelarde, your solution for the measure worked beautifully. My actual tables were more complicated than I posted but I could use COUNTROWS and the IsBlank filter with the other slicers I had on my report and get exactly what I needed. Thanks.
-Scott-
Another simpler way to do is:
Goto Relationship and join table Table A and B, hope this is already the case.
use the Table A to put into any visualization and Table B as Visual level fitler (if not required for other visuals on the report/page)
Select Blank as the filter value and you are done.
1) Go to the Query Editor - select Table A (in the Queries on the left side) - Home tab - click Merge Queries - Merge Queries As New
2) select the plant column in Table A - select Table B from the second drop-down - select Plant Column in Table B
3) Join Kind - select Left Anti(rows only in first) - OK
4) select new column - right-click - Remove
Hope this helps!
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 |
---|---|
113 | |
99 | |
80 | |
70 | |
59 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |