Matrix with Max Value in Row Highlighting and Descending Date Columns

[dwelsh]

I want to create a Matrix visual with the max value in each row highlighted and descending date column. I can get each to function independently but not together. When I try to combine, every row is highlighted (see below)

The bottom matrix has rows Stores, columns Date Desc and value Sales.

Date Desc is a column equal to the Date column and is sorted by Date Ranked column:

Date Rank = rankx('Data', [Date])

Here is the Measure for my Highlighting:

MaxInRowDateDesc = 
VAR __MaxUnits = 
MAXX(
    ALLSELECTED( 'Data'[Date Desc] ),
    [Sales]
)
RETURN
IF( 
    [Sales] = __MaxUnits,
    "Yellow"
)

Here are the Cell Element Background color settings for the Sales column:

The settings are causing every value to be highlighted yellow. What is odd, is if I am sorting Date Desc by itself, the highest value in each row works correctly. It only turns every value yellow when Date Desc is sorted by Date Rank.

Is there something I am doing incorrectly or is this a PBI limitiation? Is there a different way to acheive this?

[Anonymous]

Hi, @dwelsh 

I tried adding the index column in Power Query.

Table.Group(#"Sorted Rows", {"Store"}, {{"Index", each Table.AddIndexColumn(_,"Index",1,1), type table}})

Expand the results of the table:

Date Column sort by Index column.

Measure Color = Var _Max= MAXX(ALLEXCEPT(Data,Data[Store]),[Sales])
RETURN
IF([Sales]=_Max,"Yellow")

Is this the result you expected?

Best Regards,

Community Support Team _Charlotte

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

[Ritaf1983 · Accepted solution]

Hi @dwelsh 
Yes this is a known issue, sort column is a part of the dax query, sow when the formula ignoring the date column, it should ignore the sort column too.
Modify the formula of ranking to :

MaxInRowDateDesc = 
VAR __MaxUnits = 
MAXX(
    ALLSELECTED( 'Data'[Date Desc], 'Data'[Date rank] ),
    [Sales]
)
RETURN
IF( 
    [Sales] = __MaxUnits,
    "Yellow"
)

It should work.

For more details please refer to the linked video of Alberto Ferrari :

https://www.youtube.com/watch?v=XmGfXqWxOak

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

[dwelsh]

Thank you! That did it and seems so obvious now.