Forum Discussion

alalei's avatar
alalei
Frequent Visitor
3 years ago

Co-occurrence Matrix

Hi, I have a table with data showing tests that the transactions hit. The table looks like:   I want to create a co-ocurrence matrix visual that shows number of transactions for each test pa...
Need Help
parry2k's avatar
parry2k
Super User
3 years ago

alalei you have to create another table with unique values of test name column, it will be a disconnected table in your model. Once the new table is added, in matrix visual, use Test Name on rows from the original table, put Test Name  from this new table onto the columns, and add the following measure on the values:

 

Count Id = 
VAR __RowAndColumnValues = UNION ( VALUES ( 'Coexist Name'[Name] ), VALUES ( Coexist[Name] ) )
VAR __TableWithCount = 
CALCULATETABLE ( 
    SUMMARIZE ( 
        ALLSELECTED ( CoExist ), 
        'Coexist'[Id], 
        "@Cnt", COUNTROWS ( Coexist ) 
    ), 
    TREATAS ( __RowAndColumnValues, Coexist[Name] ) 
)
 RETURN SUMX ( FILTER ( __TableWithCount, [@Cnt] > 1 ), 1 )

 

Here is the output:

 

 

👉 Learn Power BI and Fabric - subscribe to our YT channel - @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤️