Forum Discussion
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 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
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() )
4 Replies
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() )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.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.
