Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Heat Map

I am working on a heat map and able to do the same in excel but need to push that data in power bi. We have a list of solutions and every soulution as a Privacy By Desing (PBD) and  Security By Desig...
Need Help
  • DataZoe's avatar
    DataZoe
    5 years ago

    Anonymous 

     

    I was able to get close in Power BI with this visual:

     

    Please see the attached PBIX.

     

    There were a couple things to do on this one.

     

    1. create the seperate tables for the SBD and PBD values, this allows for a context for the 0 solutions. To do this I went to Modeling ribbon > New Table and used this DAX Expression:

    PBD = DISTINCT('Table'[PBD])
    and same for SBD. For SBD I used PBD  (because there was no 0 in the data for SBD!) then renamed the column to SBD.
    2. Create the relationships between SBD - SBD and PBD - PBD:

    3. I created a measure (right-click table > New Measure) for the solutions (there has to be a 0 for the background to show!)

    Solutions = DISTINCTCOUNT('Table'[Solution])+0
     
    4. I created another measure for the Background:
    Background Color =
    var sv_sbd = SELECTEDVALUE('SBD'[SBD])
    var sv_pbd = SELECTEDVALUE('PBD'[PBD])
    var multi = sv_pbd*sv_sbd
    return
    SWITCH(
    TRUE(),
    and(sv_pbd<= 2, sv_sbd <= 2),"Red",
    and(sv_pbd>=4, sv_sbd>=4),"Green",
    "Yellow")
     
    5. I created another measure to hide the 0s by using Font Color:
    Font Color = if([Solutions]=0,[Background Color])
     
    6. On the table, I did conditional formatting:

    For background, use format by Field Value and choose the Background Color measure:

     

    For font, use the format by Field Value and choose the Font Color measure. 

     

    7. I added a shape and made the background white to cover the top left corner.

    8. I added a shape and made the column title box.

    9. I added a shape and rotated it 270 degrees to make the row title box.

    10. In View ribbon > Selection I then gave the shapes meaningful names and grouped all the visuals together so I could move them together easily. 

     

    Hope this helps!

  • Anonymous's avatar
    Anonymous
    5 years ago

    Thank you DataZoe.

DataZoe's avatar
DataZoe
Microsoft Employee
5 years ago

Anonymous 

 

I was able to get close in Power BI with this visual:

 

Please see the attached PBIX.

 

There were a couple things to do on this one.

 

1. create the seperate tables for the SBD and PBD values, this allows for a context for the 0 solutions. To do this I went to Modeling ribbon > New Table and used this DAX Expression:

PBD = DISTINCT('Table'[PBD])
and same for SBD. For SBD I used PBD  (because there was no 0 in the data for SBD!) then renamed the column to SBD.
2. Create the relationships between SBD - SBD and PBD - PBD:

3. I created a measure (right-click table > New Measure) for the solutions (there has to be a 0 for the background to show!)

Solutions = DISTINCTCOUNT('Table'[Solution])+0
 
4. I created another measure for the Background:
Background Color =
var sv_sbd = SELECTEDVALUE('SBD'[SBD])
var sv_pbd = SELECTEDVALUE('PBD'[PBD])
var multi = sv_pbd*sv_sbd
return
SWITCH(
TRUE(),
and(sv_pbd<= 2, sv_sbd <= 2),"Red",
and(sv_pbd>=4, sv_sbd>=4),"Green",
"Yellow")
 
5. I created another measure to hide the 0s by using Font Color:
Font Color = if([Solutions]=0,[Background Color])
 
6. On the table, I did conditional formatting:

For background, use format by Field Value and choose the Background Color measure:

 

For font, use the format by Field Value and choose the Font Color measure. 

 

7. I added a shape and made the background white to cover the top left corner.

8. I added a shape and made the column title box.

9. I added a shape and rotated it 270 degrees to make the row title box.

10. In View ribbon > Selection I then gave the shapes meaningful names and grouped all the visuals together so I could move them together easily. 

 

Hope this helps!