This is a follow up article from my last blog post where I demonstrated how easy it is to use two slicers to compare any two items. In the demo in my article, I specifically showed how you could compare any 2 years of choice, but the principle applies to any two products, any two customers, or any two of anything. At the bottom of that article, there was a comment from Artur asking if there was a way to limit the choices in the comparison slicer to exclude the item selected in the first slicer. E.g.
If I select Year = 2016 in slicer 1 below, then I don’t want to see Year = 2016 in the comparison slicer 2 below. I thought that was a great question/suggestion and hence that is the topic for today.
Here is the Power BI workbook for your reference Limit slicer based on another slicer.
In the video below, I go into the thought processes I went through to solve this problem, and of course show you how to do it for yourself.
The videos I share on my site are bespoke solutions to specific problems. If you would like a more comprehensive, curriculum based learning across Power Query, Power BI, DAX, and Excel, you should check out the full suite of courses Ken and I have available at Skillwave.Training.
Hey Matt – thank you very much for the video. I have a similar problem however, it’s slightly different. I have a dataset that is grouped into three categories (FCR2, FCR10, FCR30), all containing the same MAX Date value.
When FCR2 is selected in the category slicer I only want the data in the report as well as the date slicer to show all dates for that category up to 2 days before the MAX(Date) in the dataset.
When FCR10 is selected I’d like data up to 10 days before the MAX(date) and FCR30 I’d like all data up to 30 before the Max(Date).
Hopefully, this makes sense, and I think I can incorporate the idea in the video, just not entirely sure how.
Appreciate your help!
Nick
It’s hard for me to get to such requests for help. The best option is to post a question at community.powerbi.com There are lots of great people there that would love to help.
thanks matt. i was trying to do something similar but hide all dates before the initial selected date. easily handled with this method and substituting the “=” for a “>=” in the return of the Hide measure. cheers
Hi, Matt,
Thank you for the insightful tutorial.
So, I want the same. However, I don’t want 2016 selected in the slicer.
When I tried the same in my model, 2016 was hidden, which was fine. But I still saw totals for 2016 in my table.
Hi, Matt,
Thank you for the insightful tutorial.
So, I want the same. However, I don’t want 2016 selected in the slicer.
When I tried the same in my model, 2016 was hidden, which was fine. But I still saw totals for 2016 in my table.
Hello Matt,
Thank you for such a great video lesson. Would you be able to do the same thing, but this time with a Slicer with more than one value selected? The code you have used works for a single value, but I am interested in seeing how this could work for a user who has selected multiple values (Year).
Thank you
if you have multiple years selected and you want to create a text label, you can concatenate the years together with a DAX component like this
CONCATENATEX(VALUES(TABLE[YEAR]),TABLE[YEAR],”, “)
This a great solution for a comparison filter, thanks Matt. I have possibly a related issue whereby I have a single table of project names with each project name assigned a “Y” or “N”.
Project Name, Include
Project 1, Y
Project2, Y
Project 3, N
The first dropdown filter is Include (Y/N), filters the second dropdown filter Project Name
eg If a user selects Include=Y, project 1 and 2 will be displayed, then proceeds to select project 1 or 2 from the 2nd filter Project Name
The problem arises from if a user then selects “N” as the Include filter again, project 1 or 2 if selected first time round stays in the filter at the end of the results with Include=”N” at the end of the project names and wont hide any project names =”Y”
Hopefully this makes sense, as I have tried absolutely all sorts (staying away from cross-filtering) to try and get the result I need.
Your help would be greatly appreciated
Thanks, Nick
Yes, it’s a problem, I agree. If you select an item in slicer1, then make a selection in slicer 2 that makes the original selection in slicer 1 invalid, slicer 1 still shows the original selection. I’m not sure how MS could do it differently. The alternative would be to have slicer 2 override slicer 1. I could imagine a user could multi select 10 items in slicer 1, accidentally click something in slicer 2 and have the selection in slicer 1 automatically removed. I can’t think of a better design.
Greatly appreciate your help and assistance all the same Matt, I have been going a little mad trying to solve this.
Still doing great work Matt.
Nice to hear from you Steve. If we weren’t locked up, we could have a coffee
I loved this video–so helpful for users to be able to filter cleanly and have a more streamlined user experience. I am going to apply this method to my reports that feature year comparisons.
Thank you1
Great video Matt. Thanks for that.
I was surprised you were able to use selected value on compyear. I thought that only worked when a single value was selected. I guess that’s the case once you get to the rows in the slicer but just a little confusing.
In my video, the matrix had comp year on the rows of the matrix. The matrix filters the model using the rows as the filter source. The filtering behaviour of rows in a matrix is identical to a slicer albeit displaying many results at once. You can see many, but they are filtered “one at a time”. The same applies for an axis in a chart, or slices in a pie chart, etc.
That’s why it works.