The "Between dates" slicer does not respect other slicers

[JJensen]

Hi,

So I have this issue where I want to use a slicer to select a start/end date. The "between dates" date slicer.

The table is a calendar, with a year column and a quarter column. These are my dimensions.

Calendar = CALENDAR(DATE(2021, 01, 01), DATE(2023, 12, 31))
Year = YEAR([Date])
Quarter = QUARTER([Date])

OK, now add two slicers in list mode. One for the Year column, and one for the Quarter column. This effectively lets you filter the Calendar to a specific year and quarter, as well as any related facts table. So far so good...

Now add a third date slicer for the Date column in the Calendar.

- If you set the slicer to list or dropdown mode it will respect the two previous slicers and only show a list of dates from the selected year/quarter.

- If you change the slicer to "between mode" it will let you select dates from all dates in the unfiltered calendar. This is not the desired behaviour.

Is there a way to make this slicer mode update with the other slicers such that it only selects between the dates in the selected year/quarter?

 

[JJensen · Accepted solution]

Asked the question differently over here and got a solution.

[JJensen]

Actually, I should probably add som more info. Because the more I look into this the worse it gets.

1. I have a startpage in my report. It contains two slicers. Those two slicers filter for an "activity type" in my data, and a "year". So to use the report you select what avticity you are interested in and during which year.
2. The slicers are synced to all other report pages, but hiden, i.e. not visible.
3. On the second report page ther is a between slicer for dates. I noticed that this slicer does indeed behave as expected. It does respect the two other (hidden, synced) slicers, and only let me select dates in the filtered range.
4. On the third page I again try to add a between slicer. This one does not respect the other slicers, and instead lets me choose from all dates in the calendar. This is a multi year calendar and a typical activity is only 2-3 months in any year. Trying to pinpoint a subsection of less than two months on a slider with 50 monts is not useful.

To make this more interesting I deleted the between slicer from page 3. It wasn't working out anyway. This should an easy fix, I just sync the working slicer from page 2 over to page 3. That should tale care of the issue. I mean, the between slicer on page 2 already obey the slicers from page 1.

No, no, no. This does not work. The newly synced slicer on page 3 again lets me choose from all dates. 

OK, so I go to page 2 with the good slicer and select a range of interest. Then back to page 3. Slicer updated.

How about the other way? I select another range on page 3 this time. Slicer on page 2 updates. Cool.

BUT. I selected a date on page 3 that was outside the range for the good slicer on page 2. The slicer on page 2 accepted this nonsense. So I grab the slider and move it in a bit selecting a new date. When I releas the slider it moves to the end, still keeping the newly set date, but removing all the date options I just excluded. I can't choose them any more. So I move the slider a coupple mor times. Each time excluding a few more dates. Until I get in to the range of dates that originaly was on this good slicer. Then it stops excluding dates, and starts working as expected.

Power BI is inconsistent here. I litteraly copy and sync a good slicer that does work the way any reasonable persron would expect it to. But the synced copy is not syncing all the properties.

There is this little filter icon on each visual that lets you check which filters affect the visula. I check the list for the good slicer on page 2 and both slicers from page 1 shows up. As expected. Again I check the list for the broken slicer on page 3 (the one I copied over from page 2 via sync) and it does indeed not list the slicers from page 1.

The confusion is real...

[v-xiaotang]

Hi @JJensen 

Thanks for reaching out to us.

That isn't going to work if you are trying to use a slider style slicer, "between" on numbers is always going to give you all numbers. FYI: Solved: Slicer (between) is not working correctly - Microsoft Power BI Community

So it is recommended that you use other types of slicer.

Best Regards,

Community Support Team _Tang

If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

[philip_dt]

I don't think this is even relevant to the original question. In the linked post the OP is advised to use a date field in their slicer, which this OP was already doing.

But, I agree, when the slicer is set to the "Between" style, I would expect the minimum and maximum values shown to update, if another slicer changes the date range available.

For example, with the current implementation, using the same date field, you can set a date range (I'm in the UK) of 01/10/2024 31/10/2024 in your slicer with the "Between" style and choose 2024 Q1 in a "Tile" style slicer (which in the UK is 01/04/2024 to 30/06/2024) and get blank visuals because there is no overlap, regardless of any sync settings you might use.

This could be confusing to any user.

However, I appreciate the reason this doesn't work is almost certainly: "Well, how would that work backwards?" If I put 04/06/2024 to 07/07/2024 into my "Between" slicer, what does the "Tile" slicer show? 2024 Q1 AND 2024 Q2? If we're being strict that should then force the "Between" slicer to update to 01/04/2024 to 30/09/2024, which is not what you'd desire at all.

[JJensen]

Hi,

The proposed solution does not explain why the "between" slicer ignores other filters. I can understand that it would fill gaps in my filtered data. That is, after my data has been filtered the between slicer finds tha smalest and largest value in the set and fills any missing values inbetween. I do not see the benefit of the between slicer allowing me to select values outside of this range. Bacause if that made even a tiny bit of sense, it should let me select dates from the beginning of time until the end of the universe.

I shall revisit my design and find another way of filtering my data. But the key takeaway here is that the between slicer is broken. It is obvious that the slicer is able to find the smalest and largest value in my data, as well as filling in missing values. So where is the option to say, "respect other filters" before determining the range.