Conditional Formatting using DAX

[Balakrishnan_J]

Hi Community Members,

I need to use conditional format on matrix with two condition

1. if any three measure falls below threshold value in same month, i need to highlight that value red
2. if any particular measure falls below threshold value for 3 sepearate months in last 1 year, i need to highliht that value red.

Please provide some solution

[cengizhanarslan · Accepted solution]

1) Rule: “If any 3 measures are below threshold in the same month → highlight”

Any3Below_ThisMonth =
VAR t1 = IF( [On Time Delivery %] < [OTD Threshold], 1, 0 )
VAR t2 = IF( [KPI2 %] < [KPI2 Threshold], 1, 0 )
VAR t3 = IF( [KPI3 %] < [KPI3 Threshold], 1, 0 )
RETURN
IF( t1 + t2 + t3 >= 3, 1, 0 )

2) Rule: “If this measure is below threshold in 3 different months in last 12 months → highlight”

OTD_BadMonths_Last12 =
VAR EndDate = MAX('Date'[Date])
VAR MonthsToCheck =
    DATESINPERIOD( 'Date'[Date], EndDate, -12, MONTH )
RETURN
COUNTROWS(
    FILTER(
        SUMMARIZE( MonthsToCheck, 'Date'[YearMonth] ),
        CALCULATE( [On Time Delivery %] ) < [OTD Threshold]
    )
)

OTD_3BadMonths_Flag =
IF( [OTD_BadMonths_Last12] >= 3, 1, 0 )

3) Final: Color measure for conditional formatting (for OTD)

OTD_CellColor =
VAR Rule1 = [Any3Below_ThisMonth] = 1
VAR Rule2 = [OTD_3BadMonths_Flag] = 1
RETURN
IF( Rule1 || Rule2, "#FF0000", BLANK() )

[v-echaithra]

Hi @Balakrishnan_J ,

May I ask if you have resolved this issue? Please let us know if you have any further issues, we are happy to help.

Thank you.

[v-echaithra]

Hi @Balakrishnan_J ,

Thank you @techies , @cengizhanarslan  for your inputs.

We’d like to follow up regarding the recent concern. Kindly confirm whether the issue has been resolved, or if further assistance is still required. We are available to support you and are committed to helping you reach a resolution.

Best Regards,
Chaithra E.

[techies · Accepted solution]

Hi @Balakrishnan_J have tested using sample datasets, please review the attached file.

[cengizhanarslan · Accepted solution]

1) Rule: “If any 3 measures are below threshold in the same month → highlight”

Any3Below_ThisMonth =
VAR t1 = IF( [On Time Delivery %] < [OTD Threshold], 1, 0 )
VAR t2 = IF( [KPI2 %] < [KPI2 Threshold], 1, 0 )
VAR t3 = IF( [KPI3 %] < [KPI3 Threshold], 1, 0 )
RETURN
IF( t1 + t2 + t3 >= 3, 1, 0 )

2) Rule: “If this measure is below threshold in 3 different months in last 12 months → highlight”

OTD_BadMonths_Last12 =
VAR EndDate = MAX('Date'[Date])
VAR MonthsToCheck =
    DATESINPERIOD( 'Date'[Date], EndDate, -12, MONTH )
RETURN
COUNTROWS(
    FILTER(
        SUMMARIZE( MonthsToCheck, 'Date'[YearMonth] ),
        CALCULATE( [On Time Delivery %] ) < [OTD Threshold]
    )
)

OTD_3BadMonths_Flag =
IF( [OTD_BadMonths_Last12] >= 3, 1, 0 )

3) Final: Color measure for conditional formatting (for OTD)

OTD_CellColor =
VAR Rule1 = [Any3Below_ThisMonth] = 1
VAR Rule2 = [OTD_3BadMonths_Flag] = 1
RETURN
IF( Rule1 || Rule2, "#FF0000", BLANK() )