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.