Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Table Heatmap with Legends

First time Power BI (Jan 2020 version) user. I would like to create a table heatmap (and a legend) of IncidentCount group by Day and TimeOfDay as showed in the picture below.     1) I downlo...
Need Help
Icey's avatar
Icey
Community Support
6 years ago

Hi Anonymous ,

 

How about using Treemap visual to create a custom lengend?

 

1. Create a Range Table.

Range Table = 
VAR t1 =
    SELECTCOLUMNS (
        GENERATESERIES ( 0, MAXX ( 'Table', [Count Measure] ), 5 ),
        "Range1", [Value]
    )
VAR t2 =
    SELECTCOLUMNS (
        GENERATESERIES ( 5, MAXX ( 'Table', [Count Measure] ), 5 ),
        "Range2", [Value]
    )
VAR t3 =
    FILTER ( CROSSJOIN ( t1, t2 ), [Range2] - [Range1] = 5 )
VAR t4 =
    SELECTCOLUMNS (
        t3,
        "Range", [Range1] & " - " & [Range2],
        "Order", RANKX ( t3, VALUE ( [Range1] ),, DESC, DENSE )
    )
RETURN
    t4

 

 

2. Sort [Range] column by [Order] column.

 

3. Create a Measure.

1 = 1

 

4. Create a Treemap visual.

 

5. Group the Matrix visual and then Treemap visual.

 

BTW, .pbix file attached.

 

 

Best Regards,

Icey

 

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hello Icey,

    I tried your suggestion and I get a Legend 🙂  Thank you.  

    I am not familiar with DAX coding. 

    The max value in the chart is 34.  But the last legend bucket has a range of 25-30.

    I forgot to include in my original posting i have a slicer as well. 

    Can the Range Table (bucket min-max) dynamically show different ranges depending on slicer selected?  The CountMeasure could range 0 to several thousands if all slicer items are selected.

    Curious to understand how do you determine 6 buckets would represent the mix of colors in the Matrix?

     

     

     

    • Icey's avatar
      Icey
      Community Support
      6 years ago

      Hi Anonymous ,

       

      Sorry to reply late. Try to change your Range Table expression like so:

      Range Table =
VAR CountMeasure_ =
    IF (
        MOD ( MAXX ( 'Table', [Count Measure] ), 5 ) = 0,
        MAXX ( 'Table', [Count Measure] ),
        MAXX ( 'Table', [Count Measure] ) + 5
    )
VAR t1 =
    SELECTCOLUMNS ( GENERATESERIES ( 0, CountMeasure_, 5 ), "Range1", [Value] )
VAR t2 =
    SELECTCOLUMNS ( GENERATESERIES ( 5, CountMeasure_, 5 ), "Range2", [Value] )
VAR t3 =
    FILTER ( CROSSJOIN ( t1, t2 ), [Range2] - [Range1] = 5 )
VAR t4 =
    SELECTCOLUMNS (
        t3,
        "Range", [Range1] & " - " & [Range2],
        "Order", RANKX ( t3, VALUE ( [Range1] ),, DESC, DENSE )
    )
RETURN
    t4

       

      This is a bit opportunistic. The colors in the matrix are not exactly according to the legend.

       

       

      Best Regards,

      Icey

       

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

      • Anonymous's avatar
        Anonymous
        Not applicable
        5 years ago

        Hi @Icey

        Looks like i can remove the legend if i cannot show the ranges correctly.

        My HeatMap is built using Matrix with conditional background and fonts which works.

         

        FactIncident table: 1 row = 1 Incident count with other colums like like IncidentHR and IncidentDay etc..  Hence my HeatMap has 24 rows showing 12am thru 11pm (IncidentHR) and 7 columns for Sun thru Sat (IncidetnDay).

        Depending on FY or Month selected, it is possible, some Hours don't have Incidents and then my HeatMap would not show all 24 row of Hours.

        Any advise what i can do so the HeatMap will always show 24 rows and 7 columns - even if there is no data for it?  Thank you in advance.