Forum Discussion

Balakrishnan_J's avatar
5 months ago
Solved

Conditional Formatting using DAX

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...
  • cengizhanarslan's avatar
    5 months ago

    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() )