Sorting by measure across visuals for a table or matrix

[masplin]

I have a matrix with multiple measures (12) but want to sort this and other matrices by the same measure. I thought I could find a neat way to sort by measure with out going via the 3 dots for each visual using a disconnected slicer to chose the thnig to sort by.  Having 12 bookmarks is a bit irritiating.

I found this post https://stackoverflow.com/questions/67860251/power-bi-sorting-is-there-anyway-to-sort-multiple-visua...  but cant make it work

My rows are from a  table [Users].  I've tried  lots of alternatives by the result is always 1 for every row.  I'm assuming the aim is for RSorting to = the measure for each row.

Ranking Measures is my disconnected slicer. 

MeasureForSort = 
var 
WhatToSort = SWITCH(TRUE(),
                            SELECTEDVALUE ('Ranking Measures'[Measure]) = "Quoted", [Cases Quoted],
                            SELECTEDVALUE ('Ranking Measures'[Measure]) = "Quoted (£)", [Cases Quoted (£)]
)
var 
RSorting = RANKX(ALLSELECTED(Users), WhatToSort,, DESC)

return RSorting

Am I close as any advice appreciated

Mike

[OwenAuger · Accepted solution]

Hi @masplin 

I would recommend setting MeasureForSort equal to the underlying measure rather than computing a rank.

e.g.

MeasureForSort = 
SWITCH (
    SELECTEDVALUE ( 'Ranking Measures'[Measure] ),
    "Quoted", [Cases Quoted],
    "Quoted (£)", [Cases Quoted (£)]
    -- Add additional sort options here
)

Then it will work regardless of the fields on the rows of the matrix, as long as you sort by MeasureToSort by clicking column header (or ellipsis menu) on each visual.

I'm also guessing you were planning to "hide" this column by narrowing it to zero width on all the relevant visuals.

You might also need to adjust MeasureToSort so that it returns BLANK when all the measures actually displayed are blank (not sure if needed).

Example attached - will something like that work for you?

Regards

[masplin]

Ah that is so elegant and simple thank you so much.

[Anonymous]

Hi @masplin 

It seems that you have gotten a solution. Could you please mark the helpful post as Answered? It will help the others in the community find the solution easily if they face the same problem as yours. Thank you.

Best Regards    

Zhengdong Xu

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