Conditional Formatting broken by Custom Sort

[CrystalC]

Hi all,

I was able to create a custom (non-alphabetic) sort order for column headers and rows, and I also want to apply conditional formatting to color-code the 1st and 2nd ranking values across a row (Excel mockup to illustrate desired behavior).

The DAX below works as a value to conditional format by, but when I apply the custom sort order on rows and columns, the color-coding breaks and everything becomes green.  

CF Rank =

VAR cellRank = RANKX(ALLSELECTED(Mockup[Carrier]),

    CALCULATE(SUM(Mockup[Sched Dprts])),,DESC)

RETURN

    SWITCH(TRUE(),

        cellRank = 1,"Green",

        cellRank = 2,"Yellow")

Do I need to do a custom sort within the DAX expression...use a SELECTEDVALUE to get at the intersected cell in the matrix? Any suggestions greatly appreciated as I'm a relative newbie.

Thanks - Crystal

[v-jialluo-msft · Accepted solution]

^{Hi @CrystalC ,}

^{Please follow these steps:}

^{(1) My test data:}

^{(2) Add a new measure}

CF Rank =

VAR _max =

    CALCULATE (

        MAX ( 'Mockup'[Sched Dprts] ),

        ALLEXCEPT ( Mockup, 'Mockup'[Carrier] )

    )

VAR _nextmax =

    CALCULATE (

        MAX ( 'Mockup'[Sched Dprts] ),

        FILTER ( ALL ( Mockup ), [Sched Dprts] < _max )

    )

RETURN

    SWITCH ( SUM ( Mockup[Sched Dprts] ), _max, "Green", _nextmax, "Yellow" )

^{(3) The result is as follows :}

^{Best Regards,}

^{Gallen Luo}

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

[daXtreme]

Not able to troubleshoot as one would need the file with the issue to see what's going on.

[Jihwan_Kim]

Hi,

I am not sure how your datamodel looks like, but please try the below whether it suits your requirement.

CF Rank =
VAR cellRank =
    RANKX (
        ALLSELECTED ( Mockup[Carrier], Mockup[Sorting_column_carrier] ),
        CALCULATE ( SUM ( Mockup[Sched Dprts] ) ),
        ,
        DESC
    )
RETURN
    SWITCH ( TRUE (), cellRank = 1, "Green", cellRank = 2, "Yellow" )