Heat Map, Correlation Plot, Matrix Table

[Anonymous]

Hi All, 

I am going to give it my best go to articulate what I want to do. I am a relatively new user and despite finding most of power Bi really intuitive I cannot get to the bottom of this. 

I have a dataset with profiles of long term conditions (there are 😎 which are all held in fields (with binary 1/0) and the unique identifier is a patient list. Looks a bit like this;

                    Diabetes           Heart Failure         Dementia

Patient 1           1                            0                        1

Patient 2           0                            0                        1

Patient 3           1                            1                        1

What I want to achieve is somewhere between a correlation plot and a heat map. I want to know for my 8 conditions something along the lines of "how many people have both heart failure and dementia" but for all the possible combinations. (There are many thousands of records). I've achieved this before in Excel but finding it difficult in this context. 

An example of what I would like is below. 

                             Diabetes           Heart Failure             Dementia

Diabetes                     2                         1                             2

Heart Failure              1                          1                             2

Dementia                   2                          2                             3

The correlation plot is the closest I get to this - but I would like a sum of how many people have both or many conditions as opposed to correlation coefficients. 

Thanks v much for any help - all gratefully received, 

Emma 

Correlation coefficient 

[ErikHamoen]

I know this topic is quite old, but if anyone still runs into this question: This is how I solved it:
https://sidequests.blog/2023/08/01/creating-a-correlation-matrix-in-power-bi/ 

[dax]

Hi EB1991,

If possible, could you please inform me more detailed information(such as your sample data, your logic and your expecting output)? Then I will help you more correctly.

Best Regards,
Zoe Zhi

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

[Anonymous]

Hello,

I have generated some sample data. This is what I tried to illustrate in the post where it lists patient 1, patient 2 etc. It appears I cannot upload an excel file.

Second image below is where I have managed to achieve desired output using Excel. This is my expected output

The logic is - that I would like to know is within my population (Circa 20K) how many people have conditions that commonly co-occur. You can see in the second image - there are many people with both frailty and diabetes.

Thanks so much for your help - it is much appreciated,

Emma