Forum Discussion
Clearing a slicer value
Hi Anonymous ,
maybe this helps to create a default filter element, what is always valid.
Another "gotcha" from Relative filtering.
Here is what I would suggest trying.
- Add a True/False field in your date table in Power Query like
=
Duration.Days(DateTime.Date(DateTime.LocalNow()) - [Date]) <= 7
and
Duration.Days(DateTime.Date(DateTime.LocalNow()) - [Date]) > 0- Filter your slicer on that field to only show TRUE values.
If your date table was built in DAX, you'd just need similar logic to get the true/false value.
In general, try to avoid calculated columns. There are times to use them, but it is rare. Getting data out of the source system, creating columns in Power Query, or DAX Measures are usually preferred to calculated columns. See these references:
Calculated Columns vs Measures in DAX
Calculated Columns and Measures in DAX
Storage differences between calculated columns and calculated tables
Creating a Dynamic Date Table in Power Query
It sounds like there isn't a solution to this problem. I don't have a date table anywhere in the model as different report pages pull from different tables. The different tables either are daily or week ending and as such, there isn't a consolidated date table.
Calculated columns are not an option on a live connection for us either.
Finally, I created a table with 4 columns, the end of week date, a number, a flag for Prior week where it was a true/false value if the end of the week was last week. To simulate this scenario of someone looking at it last week and then looking at it this week. So I selected, week ending 23 May 2020 for when they looked at it last week. Then I filtered the slicer on the flag where it was true. I still had 23 May 2020 in the selection list and it was the selected value. When I selected 30 May 2020, 23 May went away.
It looks like it's a 'by design' thing with slicers that if the values in the slicer are no longer valid then it still stays selected. I've noticed this in the past where I have a slicer which only displays the weeks where a widget was sold. In the case where I looked for widget A, I could get 1/1/2020, 1/8/2020, 1/15/20, and select 1/8/2020. When I select widget B, and it only sold on the week ending 1/15/2020, 1/8/2020 is still selected in the filter, but it's not a valid date as evidenced when I clear the selection.
I'll mark your last reply as a solution unless Microsoft as a better answer, I'll just consider it another limitation in the tool - I mean seriously how hard is it to add a property to the card visualization so when it would return (blank) you could have it put something else in without you having to write a measure and take additional cycle time.
Thanks again.