topic Re: Conditional Formatting broken by Custom Sort in DAX Commands and Tips

Conditional Formatting broken by Custom Sort

CrystalC — Sat, 05 Nov 2022 21:23:22 GMT

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

Re: Conditional Formatting broken by Custom Sort

Jihwan_Kim — Sun, 06 Nov 2022 04:37:12 GMT

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" )

Re: Conditional Formatting broken by Custom Sort

daXtreme — Sun, 06 Nov 2022 10:25:21 GMT

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

Re: Conditional Formatting broken by Custom Sort

v-jialluo-msft — Mon, 07 Nov 2022 09:01:21 GMT

^{Hi CrystalC ,}

^{Please follow these steps:}

⁽¹⁾ ^{My test data:}

⁽²⁾ ^{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.}