Forum Discussion

Brandonkersgoed's avatar
Brandonkersgoed
Frequent Visitor
4 years ago
Solved

Power BI/Dax Matrix

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):

IDProduct
1Apple,Apple, Apple
2Apple,Chocolate
3Apple,Chocolate, Apple
4Orange, Chocolate
5Chocolate, Chocolate, Orange

Desired output:

 AppleOrangeChocolate
Apple304
Orange202
Chocolate424

 

Any help here would be greatly appreciated!

  • tamerj1's avatar
    tamerj1
    4 years ago

    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

3 Replies

  • tamerj1's avatar
    tamerj1
    Icon for Community Champion rankCommunity Champion
    4 years ago

    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
  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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