Co-occurrence Matrix

[alalei]

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 pair, like: 

Anyone know how to achieve this? My original table is a SQL view in an import mode, and it's contains almost 1M rows so the table size is quite large. I hope to not duplicate the table if possible.

[parry2k]

@alalei very hard to tell why it will not work, can you remove extra columns and stick to the original # of columns, although I don't think it will be the reason though. Do you have any filters on the page? 

[parry2k]

@alalei that is correct, coexist name = New table, coexist = original table.  Measures are global to the model, doesn't matter where you put those. 

Now, what is not working? Can you give an example with data? 

[alalei]

@parry2k , the desired matirx for dummy data is shown as expected, but when I apply the same method in real data, the count for each pair is larger than the actual count. My original table has a few more descriptive columns, but the combination of trx id and test name in each row is unique.

[alalei]

Hi @parry2k ,

Thank you so much for the quick response! Would you mind specifing the 'Coexist Name' and 'Coexist'? I replaced 'Coexist Name' with 'newTable' and 'Coexist' with my original table name but didn't get the desired results. Should the meausre be created in the original table?

Much appreciated.

alalei

[parry2k]

@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! ❤️