Row Based Conditional Formatting 05-13-2020 13:38 PM - last edited 05-13-2020 14:54 PM

[Greg_Deckler]

Unlike traditional conditional formatting that conditionally formats colors low to high based upon the values in columns or all values in all cells, these measures and pattern conditionally format colors low to high based upon the values within the rows. This is implemented as a variation on the disconnected table trick.

Color Measure1 = 
    VAR __Table =
        {
            [kpi1],
            [kpi2],
            [kpi3],
            [kpi4]
        }
RETURN
    COUNTROWS(FILTER(__Table,[Value]<=[KPI to Show]))


Color Measure2 = 
    VAR __Customer = MAX('Table'[Customer])
    VAR __Table =
        ADDCOLUMNS(
            DISTINCT(ALL('Table'[Customer])),
            "Value",CALCULATE([kpi3],'KPI'[KPI]="kpi3",'Table'[Customer]=EARLIER([Customer]))
        )
    VAR __Max = MAXX(__Table,[Value])
RETURN
    IF(MAXX(FILTER(__Table,[Value] = __Max),[Customer]) = __Customer,1,0)


Color Measure3 = 
    VAR __Customer = MAX('Table'[Customer])
    VAR __Table =
        ADDCOLUMNS(
            DISTINCT(ALL('Table'[Customer])),
            "Value",CALCULATE([kpi4],'KPI'[KPI]="kpi4",'Table'[Customer]=EARLIER([Customer]))
        )
    VAR __Current = [kpi4]
RETURN
    COUNTROWS(FILTER(__Table,[Value]<=__Current))

eyJrIjoiNjVjMDcwMWYtODgzOC00ZWVjLTkxMWItMjk2OGM4YmM5MTdlIiwidCI6IjRhMDQyNzQzLTM3M2EtNDNkMi04MjdiLTAwM2Y0YzdiYTFlNSIsImMiOjN9