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!