cancel
Showing results for
Did you mean:
Highlighted
Helper I

dynamic sort by measure filter

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

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Support

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!
5 REPLIES 5
Highlighted
Super User IV

@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...

---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! DAX Cookbook from Packt
Over 120 DAX Recipes!

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

Proud to be a Super User!

Highlighted
Super User IV

@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.

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Highlighted
Helper I

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.

Highlighted
Helper I

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

Highlighted
Community Support

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!

Announcements

Power Platform Community Conference

Check out the on demand sessions that are available now!

Microsoft Power Platform Communities

Check out the Winners!

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors