Can a slicer display (only display not use) values from a corresponding column within the same table or are there overriding slicers?

Question

Hi Stack Overflow PowerBI/DAX Community,

Tried searching through multiple forums and coming up with ways to go about it but can’t quite find a solution that works perfectly. I did come up with some ideas but unsure if it’s feasible, so let me know your thoughts. Both a screenshot and sample PBIX file from a public OneDrive is provided below for clarity. The file uses Import mode for troubleshooting, but my report is in Direct Query mode (more on that later).

Description: My report displays historical data and its pre-selected to display 2 dates as the default view. The table has 2 columns: ‘Day’ representing a number for a day and ‘Date’ the date of that day. Both is in reference to each other (e.g. 0 = 11/06/23, 1 = 11/05/23, etc). The pre-selection requirement must be from the ‘Day’ column because the database updates the data through that column only.

Issue: Due to the pre-selection requirement of slicing by the ‘Day’ column, having a date slicer for my end users limits their selection to 2 of 4 dates, but they should have the ability to select from all 4 dates.

Ideal Solution (Slicer Value Reference): If I continue to use the slicer with ‘Day’ values (0, 1, 2, 3), is there a way to show the corresponding ‘Date’ values instead? Unbeknownst to the end user, they see only the “dates” but actually slicing the ‘Day’ values, which makes it easy for me so I can keep the default slicer to 2 ‘Day’ values (0 and 1).

Workaround Solution (Slicer Override): Have 2 slicers, one for ‘Day’ column and the other for ‘Date’ column, then have my pre-selected ‘Day’ slicer hidden from view. The ‘Date’ slicer is visible and have all 4 dates available that can override the ‘Day’ slicer. I tried to do this using edit interactions, but it doesn’t work that day.

Unpopular Workaround (‘Day’ value under ‘Date’ value): Not ideal and haven’t had a chance to fully test it but sounds like it’ll work. Add the ‘Date’ column as a slicer value first, then ‘Day’ column, which becomes acts as the value underneath the ‘Date’ value, then pre-select my ‘Day’ values. The user sees a dropdown arrow for each date, but at least sees the dates.

Question: Based off my problem, are either of the first two solutions presented feasible and if so, how? If not, am I stuck trying the third unpopular workaround or do you recommend another method? Again, screenshot and PBIX file link below.

Comment: Since my report is through Direct Query mode, I’ve heard others responding to a problem like mines and said to create a separate non-related table, setup a uni-directional relationship cardinality from the non-related table to the ‘Day’ column, then slice against the non-related table. Not sure if that’s the only solution or is there a measure that can cleverly display ‘Date’ values for ‘Day’ values…Again, thank you in advance.

Public OneDrive of PBIX file: Slicer Value Reference or Overriding Slicer.pbix

Answer 1

Your Unpopular Workaround (‘Day’ value under ‘Date’ value) is the best/easiest option and it works. You could rename 'Day' in the Slicer field to 'days ago' and some users may find it useful.
Unfortunately, this option doesn't work as the slicer "remembers" the parent selection as well.

Let me simplify your question - how can I set default 'dynamic' values for slicers? There isn't a feature for this yet in Power BI. There are one or two custom visuals in the marketplace that claim they do. Look for "Preselected Slicer" in More visuals in the home ribbon, then From AppSource.

One alternative to this, in your case for example, is to have an 'adjusted' date column...

Day	Date	Adjusted
0	7-Nov-23	Today
1	6-Nov-23	Yesterday
2	5-Nov-23	5-Nov-23

And then you use the "adjusted" column for your slicer. I know this won't help you with your DirectQuery but throwing it out there. If this option is appealing, then perhaps see if you can create a 'Mixed' storage mode where you can have DirectQuery and local/imported tables in the same dataset.