Power BI/Dax Matrix

[Brandonkersgoed]

Hey Power BI Community,

I'm struggling trying to put a matrix together, right now I have a table, I want to make a matrix showing a count with how many items are shown with eachother in each ID. Table 1 exists in my data currently, however I'm not sure whether I need to make a new table in Power BI, or make a matrix using a chart. 

I tried making a chart with the product as both row and column, but not sure how to get the count right (I got the total count of rows instead of the unique products in the matrix).

Table 1 (data):

ID	Product
1	Apple,Apple, Apple
2	Apple,Chocolate
3	Apple,Chocolate, Apple
4	Orange, Chocolate
5	Chocolate, Chocolate, Orange

Desired output:

	Apple	Orange	Chocolate
Apple	3	0	4
Orange	2	0	2
Chocolate	4	2	4

Any help here would be greatly appreciated!

[Anonymous]

Hi  @Brandonkersgoed ,

Here are the steps you can follow：

1. In Power Query - select [Product]. - Split Column - By Delimiter.

Result:

2. Select the column marked in yellow – Unpivot Columns.

3. Select the [Value] column in the new table - Transform - Trim.

4. Create a table with Enter data.

5. Create measure.

Measure 2 =
var _orange=
CALCULATE(DISTINCTCOUNT('Table'[ID]),FILTER(ALL('Table'),'Table'[Value]=MAX('Table'[Value])&&'Table'[Value]=MAX('Table2'[Product])))
var _apple=
CALCULATE(DISTINCTCOUNT('Table'[ID]),FILTER(ALL('Table'),'Table'[Value]=MAX('Table'[Value])))
return
IF(MAX('Table2'[Product])="Orange",_orange,IF(MAX('Table2'[Product])="Apple"&&MAX('Table'[Value])="Apple",_apple+1,_apple))

6. Result:

If you need pbix, please click here.

Power BI Dax Matrix.pbix

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[tamerj1 · Accepted solution]

Hi @Brandonkersgoed 
Please refer to attached file with the solution

Measure = 
VAR Product1 = SELECTEDVALUE ( Filter1[Product] )
VAR Product2 = SELECTEDVALUE ( Filter2[Product] )
RETURN
    COUNTROWS (
        FILTER (
            'Table',
            CONTAINSSTRING ( 'Table'[Product], Product1 )
                && CONTAINSSTRING ( 'Table'[Product], Product2 )
        )
    ) + 0

[Brandonkersgoed]

Thanks @tamerj1!

Worked exactly how I needed it to for my set.

Truly appreciate the help