Display Differences Between 2 Matrices

[bbajuscak]

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!

[bbajuscak]

@AllisonKennedy 
@lbendlin 


Here is a link to the pbix file I am working with:

WorkBreakdownStructureV4.pbix

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

[lbendlin]

Link requires access. please check.

[bbajuscak]

@lbendlin 

corrected access, please try this link

WorkBreakdownStructureV4.pbix

[lbendlin]

still the same.

[bbajuscak]

Hmmm, 1st time trying to share out a pbix...can you try this link and let me know if it works correctly?

@lbendlin 

https://1drv.ms/u/c/af6876c99b2bc15d/EdJjZSP_W5RIi8hw2oIRwmgBQPN1-QSL9bgD0SKPVrV1DQ

[lbendlin]

This time it worked. Can you please explain that data model to me?  Why all the bidirectionals?

[bbajuscak]

I'd like to give you a logical reason for the bidirectional relationships, but I didn't realize they were set up that way.

Data is coming from 2 seperate systems, I am attempting to compare the WBS out of each to verify alignment between the two.

[lbendlin · Accepted solution]

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)

[bbajuscak]

Is this using DAX or creating a measure?

[lbendlin]

Yes and no.  This is DAX but it results in a table. A measure will be unable to show the differences, it will only be able to indicate that there is a difference (rowcount more than 0)

[bbajuscak]

I have eliminated the bidirectional relationships with my tables. When I try to run the query I receive an error, Resource Governing. Any ideas on how to get around this?

[lbendlin]

Get a better computer, or use fewer columns for the comparison.  You could also load the tables into a real database and do anti-joins.

[AllisonKennedy]

@bbajuscak  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. 

[lbendlin]

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-...
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...