Forum Discussion
Advocate IMatrix with Max Value in Row Highlighting and Descending Date Columns
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?
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.
3 Replies
- Ritaf1983
Super User
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.
dwelshThank you! That did it and seems so obvious now.
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.
