Table Column Conditional Formatting based on Static Target Range Table

Data Visualizations
1

Hi,

For the following table I’m wanting to conditional format the Total HPPD column based on the target range in the Min/Max HPPD columns (ie. in the below Med - Respiratory would highly red, and Med - Neurology would highly blue).
image

The Min/Max HPPD are static ranges for each different Patient Type (~250 types) contained within a dimensions table, whilst the Total HPPD is a measure created from the main fact table (an export from the source system).

Attached is an example PBI file.

An guidance will be greatly appreciated.
Thanks.
Doug.
Conditional Formatt Range.pbix (341.1 KB)

2

Hello @DougK,

Thank You for posting your query onto the Forum.

To apply the conditional formatting on the “Total HPPD”. Below is the measure provided for the reference -

Total HPPD Formatting - Harsh = 
VAR _Total_HPPD = 
SUMX(
    SUMMARIZE(
        HPPD , 
        HPPD[Patient Type Name] , 
        "Total HPPD" , 
        [HPPD] ) , 
    [Total HPPD] )

VAR _Max_HPPD = 
IF( ISBLANK( [Total HPPD] ) , 
    BLANK() , 
    MAX( PtTypeRanges[Maximum HPPD] ) )

VAR _Min_HPPD = 
IF( ISBLANK( [Total HPPD] ) , 
    BLANK() , 
    MIN( PtTypeRanges[Minimum HPPD] ) )

VAR _Results = 
SWITCH( TRUE() , 
    _Total_HPPD > _Max_HPPD , "#FF0000" , 
    _Total_HPPD < _Min_HPPD , "#0000FF" , 
    _Total_HPPD >= _Min_HPPD && _Total_HPPD <= _Max_HPPD , "#FFFF00" )


RETURN
_Results

Now, select the measure “Total HPPD” and apply the conditional formatting using the “Field Value” option. Below is the screenshot of the conditional formatting as well as of the final results provided for the reference -

Conditional Formatting
Conditional Formatting972×376 14.9 KB

Final Results
Final Results795×494 22.6 KB

I’m also attaching the working of the PBIX file for the reference purposes.

Hoping you find this useful and meets your requirements that you’ve been looking for. :slightly_smiling_face:

Thanks and Warm Regards,
Harsh

Conditional Formatt Range - Harsh.pbix (341.6 KB)

1 Like
3

Brilliant; Thanks a heap :grinning:
I’ve a lot to learn before I would have figured that out.
Thank you.

4

Hello @DougK,

You’re Welcome!!!

I’m glad that I was able to assist you.

Thanks and Warm Regards,
Harsh