dynamic sort by measure filter

[RajeshLM]

Hi All,

how can we achieve below requirement?

there is a metric slicer, if i choose 'AVERAGE' the it should sort Average measure by ASC order, elseif i choose 'DISMISSALS' then it should sort Dismissals measure by DESC order.

i am not able to achieve the sorting requirement

Data:

Batsman             Average Dismissals

Virat Kohli	100	5
Kedar Jadhav	62	1
Rohit Sharma	51.9	10
Shikhar Dhawan	34.4	12
MS Dhoni	24.77	9

Chart:

Thanks,

Raj

[amitchandak]

@RajeshLM , Are able to sort on the measure(Using the three dots) on the visual. then it should sort or the whatever has been selected.

[RajeshLM]

No Amit, using those 3 dots i cannot sort the way i need.

 if i choose 'AVERAGE' the it should sort common measure(Measure) by ASC order, elseif i choose 'DISMISSALS' then it should sort common measure(Measure) by DESC order

[v-kelly-msft · Accepted solution]

Hi @RajeshLM ，

First create a slicer table as below:

Then create 2 measures as below:

Measure = 
SWITCH(SELECTEDVALUE('Table (2)'[Slicer]),"Average",MAX('Table'[Average ]),"Dismissals",MAX('Table'[Dismissals]),BLANK())

Measure 2 = 
SWITCH(SELECTEDVALUE('Table (2)'[Slicer]),"Average" ,
RANKX(ALLSELECTED('Table'),'Table'[Measure],,ASC,Dense),
"Dismissals",
RANKX(ALLSELECTED('Table'),'Table'[Measure],,DESC,Dense))

And you will see:(make measure 2 as a tooltip field)

For the related .pbix file,pls see attached.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[Greg_Deckler]

@RajeshLM - I believe that you would need a disconnected table and a single measure that switches between the average and dismissals based upon the slicer selection. In general, to use a measure in that way, you need to use the Disconnected Table Trick as this article demonstrates: https://community.powerbi.com/t5/Community-Blog/Solving-Attendance-with-the-Disconnected-Table-Trick...

[RajeshLM]

Thanks Greg.

Infact i have created a disconnected table and a single measure that switches between the average and dismissals based upon the slicer selection. but i am stuck with the sorting - Average should sort by Asc and Dismissals should sort by Desc.