Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
Anonymous
Not applicable

Pivot table and create co-occurencies heatmap

I have the following table

 

RECORD_IDTAG
1TAG1
1TAG2
2TAG1
2TAG3

 

I would like to get a heatmap of co-occurrencies. In matrix form, it would be

 

 TAG1TAG2TAG3
TAG1count of record_id with tag1count of record_id with tag1 and tag2count of record_id with tag1 and tag3
TAG2count of record_id with tag2 and tag1count of record_id with tag2 count of record_id with tag2 and tag3
TAG3count of record_id with tag3 and tag1count of record_id with tag3 and tag2count of record_id with tag3

 

which, in this case, is a symmetric matrix. Then I would like to color the cell based on the value.

I guess to obtain that matrix I should pivot my table on TAG and use those counts as values, but I am not familiar with PowerBi and DAX, so how can I achieve this? and how can I get the coloured heatmap? Is there a more direct way to achieve my goal?

1 ACCEPTED SOLUTION
Greg_Deckler
Super User
Super User

@Anonymous - See PBIX attached below sig. Tables (31) and (31a). First, create disconnected table from original like this:

Table (31a) = DISTINCT('Table (31)'[TAG])

Then this measure:

Measure 31 = 
    VAR __Table1 = SELECTCOLUMNS('Table (31)',"Record",[RECORD_ID])
    VAR __Table2 = SELECTCOLUMNS(FILTER(ALL('Table (31)'),[TAG]=MAX('Table (31a)'[TAG])),"Record",[RECORD_ID])
RETURN
    COUNTROWS(INTERSECT(__Table1,__Table2))

Put [TAG] from (31) in Rows of a Matrix, [TAG] from (31a) in Columns, Measure 31 in Values.

 

 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

2 REPLIES 2
Greg_Deckler
Super User
Super User

@Anonymous - See PBIX attached below sig. Tables (31) and (31a). First, create disconnected table from original like this:

Table (31a) = DISTINCT('Table (31)'[TAG])

Then this measure:

Measure 31 = 
    VAR __Table1 = SELECTCOLUMNS('Table (31)',"Record",[RECORD_ID])
    VAR __Table2 = SELECTCOLUMNS(FILTER(ALL('Table (31)'),[TAG]=MAX('Table (31a)'[TAG])),"Record",[RECORD_ID])
RETURN
    COUNTROWS(INTERSECT(__Table1,__Table2))

Put [TAG] from (31) in Rows of a Matrix, [TAG] from (31a) in Columns, Measure 31 in Values.

 

 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

Thanks @Greg_Deckler! may I ask you to elaborate a bit more on what the variable __table2 is? I am not quite sure what happens there, and I'd like to understand the process. Thank you. 

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors