Forum Discussion
Display Differences Between 2 Matrices
I have 2 seperate matrices, each built from different data sources, using different tables and table structures. Tables making up each matrix are not related to one another.
Each matrix is a heirarchy showing the work breakdown structure ( stories roll up into epics, epics roll up into projects, projects roll up into initiatives) The heirarchy in each is important.
How can I highlight the differences between the matrices? Matrix 1 being the source of truth.
Ideally I'd like to highlight in Red any items that is out of place from Matrix 1.
I need to be able to visualize what items in matrix 2 are different from matrix 1. Different being both what is missing as well as what items are in a different heirarchy from matrix 1 vs matrix 2.
Below is a screenshot of the 2 matrices currently built out and needing to compare. ie. visualize when a story is assigned to a different epic, or different project and see that heirarchy.
My tables and table structure is different for each matrix as seen below. Circled Red are tables used to create matrix 1. Circled Green are tables used to create matrix 2.
I'd like to create a third, or a new matrix, which displays the heirarchy for only those that are different between the 2 initial matrices.
Any insight is greatly appreciated!
You will want to clean this up as it will have a massive impact on performance.
Here is the general approach for the comparison
DEFINE VAR a = SELECTCOLUMNS(SUMMARIZECOLUMNS( 'KI - Epics'[Initiative Name], 'KI - Epics'[Project Name], 'KI - Story'[Key + Status], 'KI - Epics'[Key + Status]), "Column1", 'KI - Epics'[Initiative Name], "Column2", 'KI - Epics'[Project Name], "Column3", 'KI - Story'[Key + Status], "Column4", 'KI - Epics'[Key + Status]) VAR b = SELECTCOLUMNS(SUMMARIZECOLUMNS( 'Initiatives - Issues'[SUMMARY], 'Project - Issues'[SUMMARY], 'Epics - Issues'[Key + Status], 'Stories - Issues'[Key + Status]), "Column1", 'Initiatives - Issues'[SUMMARY], "Column2", 'Project - Issues'[SUMMARY], "Column3", 'Epics - Issues'[Key + Status], "Column4", 'Stories - Issues'[Key + Status]) EVALUATE EXCEPT(a,b)
14 Replies
Use EXCEPT().
If you like help please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information. Do not include anything that is unrelated to the issue or question.
Please show the expected outcome based on the sample data you provided.
Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523bbajuscak The only way to highlight differences is to have the data related in some way. One option is to use Power Query and do a merge - you could do a 'left outer' merge to find only things that are in the first table and not in the second table.
AllisonKennedy
lbendlin
Here is a link to the pbix file I am working with:I would like to end up with 2 matrices, each only showing the wbs which is not present in the other matrix, not the entire wbs
Link requires access. please check.
