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
v-echaithra
5 months agoCommunity Support
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.