Conditional Formatting with DAX

Hi Anonymous ,

Let the min in red and max in green, and if "Actual" value is zero, then this period in red, which result basd on [sales_] of each rows.

Please try this code to do that:

Measured =
VAR _s =
    FILTER (
        SUMMARIZE (
            ALLSELECTED ( 'Data Table'[Year] ),
            [Year],
            "Sale",
                IF (
                    COUNTROWS (
                        FILTER ( 'Data Table', 'Data Table'[Version] = "Actual" && [Sales] = 0 )
                    ) > 0,
                    BLANK (),
                    [Sales_]
                )
        ),
        [Sale] > 0
    )
VAR _ss =
    FILTER (
        SUMMARIZE (
            CALCULATETABLE (
                'Data Table',
                ALLSELECTED ( 'Data Table'[Year] ),
                'Data Table'[Quarter] <> "FY"
            ),
            [Year],
            "Sale",
                IF (
                    COUNTROWS (
                        FILTER ( 'Data Table', 'Data Table'[Version] = "Actual" && [Sales] = 0 )
                    ) > 1,
                    0,
                    [Sales_]
                )
        ),
        [Sale] > 0
    )
VAR _max =
    IF (
        SELECTEDVALUE ( 'Data Table'[Quarter] ) = "FY",
        MAXX ( TOPN ( 1, _ss, [Sale], DESC ), [Year] ),
        MAXX ( TOPN ( 1, _s, [Sale], DESC ), [Year] )
    )
VAR _min =
    IF (
        SELECTEDVALUE ( 'Data Table'[Quarter] ) = "FY",
        MAXX ( TOPN ( 1, _ss, [Sale], ASC ), [Year] ),
        MAXX ( TOPN ( 1, _s, [Sale], ASC ), [Year] )
    )
RETURN
    SWITCH (
        SELECTEDVALUE ( 'Data Table'[Year] ),
        _max, "green",
        _min, "red",
        "black"
    )

Result:

Pbix in the end you can refer.

Best Regards

Community Support Team _ chenwu zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.