Forum Discussion

CrystalC's avatar
CrystalC
New Member
3 years ago
Solved

Conditional Formatting broken by Custom Sort

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's avatar
    v-jialluo-msft
    3 years ago

    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.

3 Replies

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Super User
    3 years ago

    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" )
  • daXtreme's avatar
    daXtreme
    Solution Sage
    3 years ago

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

  • v-jialluo-msft's avatar
    v-jialluo-msft
    Community Support
    3 years ago

    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.