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:
| List 1 | List 2 | List 3 | ||
| black | white | black | ||
| white | red | purple | ||
| red | green | green | ||
| green | white | |||
| blue | blue | |||
| orange |
I'm trying to create a (matrix?) visual that shows the number of common colours. So, the output would look something like this:
| List 1 | List 2 | List 3 | |
| List 1 | 5 | 3 | 4 |
| List 2 | 3 | 3 | 2 |
| List 3 | 3 | 2 | 5 |
I can work out how to get the number of common elements using countrows(intersect(list1,list2)) but don't know how I would do it without having to write a measure for each intersection and then I have no idea how to show it on a visual.
Any help would be great - thanks.
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
4 Replies
Hi,
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.
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
