Forum Discussion
Unable to sort matrix visual values by month
The matrix does not pass the clicked column's filter context into the sort measure. When you sort a matrix by a measure, the engine evaluates that measure once per row using the full row context (effectively the row total, with all columns visible), so SELECTEDVALUE on Calendar[Month] returns blank and the measure falls back to the year total. That is the same behavior whether you click a column header or use "Sort by" in the visual menu.
The standard workaround is to drive the sort from a slicer or a field parameter rather than the column you click. Add a single select slicer with the month name, and have your sort measure read from that slicer:
Sort by Month = VAR _m = SELECTEDVALUE ( 'Sort Selector'[Month] ) RETURN CALCULATE ( [Sales], 'Calendar'[Month] = _m )
Then sort the matrix by [Sort by Month] descending. When the user picks a month in the slicer, the rows reorder by that month's sales.
If this helped, a thumbs up and accepting the solution would be appreciated.
Best,
Shai Karmani
The matrix does not pass the clicked column's filter context into the sort measure. When you sort a matrix by a measure, the engine evaluates that measure once per row using the full row context (effectively the row total, with all columns visible), so SELECTEDVALUE on Calendar[Month] returns blank and the measure falls back to the year total. That is the same behavior whether you click a column header or use "Sort by" in the visual menu.
The standard workaround is to drive the sort from a slicer or a field parameter rather than the column you click. Add a single select slicer with the month name, and have your sort measure read from that slicer:
Sort by Month = VAR _m = SELECTEDVALUE ( 'Sort Selector'[Month] ) RETURN CALCULATE ( [Sales], 'Calendar'[Month] = _m )
Then sort the matrix by [Sort by Month] descending. When the user picks a month in the slicer, the rows reorder by that month's sales.
If this helped, a thumbs up and accepting the solution would be appreciated.
Best,
Shai Karmani
Hi Shai,
Thank you very much! I used a slicer and it is now sorting correctly.