Hello Mynda,
I hope this message finds you well.
First off, let me state that your dashboard tutorials are insightful and thank you for your dedication.
I have a question about slicers and options buttons. I would like to make my slicer respect the options buttons when sorting each column of data into either "ascending" or "descending" order. Is there a formula or DAX measure you could share that will be useful?
A copy of the image is attached.
Thank you.
Warmest Regards,
Eddie
Hi Eddie,
Welcome to our forum!
The sort order of the items displayed in the Slicer is set in the model using Sort By. It's not something that can respond to a option button.
Perhaps a workaround is to duplicate the field you want to sort and insert a Slicer for both fields. Have one Slicer sorted in ascending order and another in descending order. Overlay them on top of one another so you can only see the top Slicer. Then write some VBA to surface the relevant Slicer to the top when the option button is selected.
Mynda
Hi Mynda,
Thank you for the swift response.
Would you be so kind to share how to use the Sort By function you mentioned? I saw a "Slicer_Sort_By" when attempting to write a formula, but it generated an error.
One of your Dashboard GIFs had Option buttons and a slicer for sorting columns of data, hence the question.
If not, I totally understand, and I would work on writing a VBA code to surface the relevant slicer.
Thanks once again.
Warmest Regards,
Eddie
Hi Eddie,
I'm starting to wonder if I've correctly understood what you're wanting to do. You said "I would like to make my slicer respect the option buttons when sorting each column of data into either "ascending" or "descending" order". From this I assumed that you wanted to change the sort order of the items displayed in the Slicer shown in your screenshot (Job Title, Name, Years of Service), when the radio buttons are selected. i.e. The Slicer displays the items as follows:
In ascending order:
- Job Title
- Name
- Years of Service
In descending order:
- Years of Service
- Name
- Job Title
However, I'm starting to think that's not what you meant. Perhaps you could clarify what you're after, so I know if I'm on the right track.
Mynda
Hi Mynda,
Thanks for the response once again, and apologies for the confusion.
I have attached a screenshot of one of your dashboards for clarification.
On the Region Dashboard, how did you create the table on the far left with the Option buttons for sorting and slicer as headings for the table underneath?
Looking forward to your earliest response.
Warmest Regards,
Eddie
Ah, I teach that to my Excel Dashboard course members. I use the SORTBY function to sort based on the item selected in the Slicer. If you don't have Microsoft 365 and the SORTBY function, then there is another approach, but it's more complex. I suggest you take my course and you can learn step by step.
Mynda
