cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper I
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 Kohli1005
Kedar Jadhav621
Rohit Sharma51.910
Shikhar Dhawan34.412
MS Dhoni24.779

 

Chart:

chart.PNGchart2.PNG

 

Thanks,

Raj

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted

Hi @RajeshLM

 

First create a slicer table as below:

Annotation 2020-09-07 140725.png

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)

Annotation 2020-09-07 143702.png

 

Annotation 2020-09-07 140854.png

For the related .pbix file,pls see attached.

 

Best Regards,
Kelly
Did I answer your question? Mark my post as a solution!

View solution in original post

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

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

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

Hi @RajeshLM

 

First create a slicer table as below:

Annotation 2020-09-07 140725.png

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)

Annotation 2020-09-07 143702.png

 

Annotation 2020-09-07 140854.png

For the related .pbix file,pls see attached.

 

Best Regards,
Kelly
Did I answer your question? Mark my post as a solution!

View solution in original post

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors