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!
Welcome to the Power BI Community Show! Jeroen ter Heerdt talks about the importance of Data Modeling.
Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!
User | Count |
---|---|
374 | |
104 | |
68 | |
57 | |
51 |
User | Count |
---|---|
336 | |
121 | |
88 | |
71 | |
63 |