Forum Discussion
Matrix visual showing intersects between lists / single column tables
This feels like it should be easy but I just can't get it. I have a number of lists that are DAX calculated tables with a single column. For the sake of simplicity imagine they are like this: ...
Anonymous
Create the table to get the cross joins of the names and count the common colours for new column:Table 2 = ADDCOLUMNS( CROSSJOIN( SELECTCOLUMNS( ALLNOBLANKROW('Table'[Name]), "Name1" , 'Table'[Name] ), SELECTCOLUMNS( ALLNOBLANKROW('Table'[Name]), "Name2" , 'Table'[Name] ) ), "Common Colors" , VAR __T1 = SELECTCOLUMNS( FILTER( ALL('Table') , 'Table'[Name] = [Name1] ) , 'Table'[Colour] ) VAR __T2 = SELECTCOLUMNS( FILTER( ALL('Table') , 'Table'[Name] = [Name2] ) , 'Table'[Colour] ) VAR __RESULT = COUNTROWS( INTERSECT( __T1 , __T2 )) RETURN __RESULT )
Create a simple measure and visualize in a matrix:Count of Common Colors = SUM( 'Table 2'[Common Colors] )
File attached
Ashish_Mathur
Super User
Super UserHi,
I have solved a similar problem in which the result was generated from a raw data table rather then 3 calculated tables. If you are looking at arriving at your end result from a single table, then share some data to work with.
Great - thanks!
The raw data would look something like this:
Colour Name black Andy black Gemma blue Andy blue Gemma green Andy green Claire green Gemma orange Gemma purple Gemma red Andy red Claire white Andy white Gemma white Claire And then the output visual would be:
Andy Claire Gemma Andy 5 3 4 Claire 3 3 2 Gemma 3 2 5 - Fowmy
Anonymous
Create the table to get the cross joins of the names and count the common colours for new column:Table 2 = ADDCOLUMNS( CROSSJOIN( SELECTCOLUMNS( ALLNOBLANKROW('Table'[Name]), "Name1" , 'Table'[Name] ), SELECTCOLUMNS( ALLNOBLANKROW('Table'[Name]), "Name2" , 'Table'[Name] ) ), "Common Colors" , VAR __T1 = SELECTCOLUMNS( FILTER( ALL('Table') , 'Table'[Name] = [Name1] ) , 'Table'[Colour] ) VAR __T2 = SELECTCOLUMNS( FILTER( ALL('Table') , 'Table'[Name] = [Name2] ) , 'Table'[Colour] ) VAR __RESULT = COUNTROWS( INTERSECT( __T1 , __T2 )) RETURN __RESULT )
Create a simple measure and visualize in a matrix:Count of Common Colors = SUM( 'Table 2'[Common Colors] )
File attachedPerfect - worked like a dream - thanks!