Forum Discussion

bbajuscak's avatar
bbajuscak
Icon for Helper I rankHelper I
1 year ago
Solved

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 heirarc...
Need Help
  • lbendlin's avatar
    lbendlin
    1 year ago

    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's avatar
bbajuscak
Icon for Helper I rankHelper I
1 year ago

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's avatar
lbendlin
Icon for Super User rankSuper User
1 year ago

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's avatar
    bbajuscak
    Icon for Helper I rankHelper I
    1 year ago

    Is this using DAX or creating a measure?

  • lbendlin's avatar
    lbendlin
    Icon for Super User rankSuper User
    1 year ago

    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's avatar
    bbajuscak
    Icon for Helper I rankHelper I
    1 year ago

    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's avatar
    lbendlin
    Icon for Super User rankSuper User
    1 year ago

    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.