Forum Discussion
Balakrishnan_J
5 months agoHelper I
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...
- 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() ) - 5 months ago
cengizhanarslan
5 months agoSuper User
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() )