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 ,

 

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.