topic Re: slicers automatically selected based on another slicer selection in DAX Commands and Tips

slicers automatically selected based on another slicer selection

bharosha — Thu, 05 Jun 2025 18:51:39 GMT

I have a table that has id, open date and close date. I need to be able to select a date on close date filter, depending upon the selection, open date slicer gets automatically selected. Is there a way to do so.

Re: slicers automatically selected based on another slicer selection

Nasif_Azam — Thu, 05 Jun 2025 19:13:28 GMT

Hey bharosha ,

Power BI slicers do not natively support automatic cross-filtering between two slicers based on unrelated columns (e.g., selecting a Close Date and having Open Date slicer reflect that). However, there are a few workarounds depending on the complexity and flexibility you need.

Objectives:

You want:

A Close Date slicer that the user interacts with.
An Open Date slicer that is automatically filtered based on the selected Close Date range (e.g., if a Close Date is selected as 2024-06-01, only Open Dates on or before 2024-06-01 should appear in the slicer).

Solution Using a Date Table and DAX

Step-1: Create a Calendar Table

Create a separate DateTable:

DateTable = CALENDAR(MIN('YourTable'[OpenDate]), MAX('YourTable'[CloseDate]))

Step-2: Create Relationships

No direct relationship from DateTable to YourTable is needed for slicers.
But for measures, you might still relate it if needed.

Step-3: Add Calculated Columns

Add a column to flag whether a date is eligible for filtering:

IsOpenDateValid = 
VAR SelectedCloseDate = MAX('YourTable'[CloseDate])
RETURN 
    IF('DateTable'[Date] <= SelectedCloseDate, 1, 0)

But this won’t auto-refresh based on slicer selection because slicers operate before filters/measures.

Workaround with Sync Using a Visual and Measure

Step-1: Create Measures

Create a measure to capture selected close date range:

SelectedCloseDate = MAX('YourTable'[CloseDate])

Then, create a measure to filter the open date:

FilterOpenDates = 
IF(
    MAX('YourTable'[OpenDate]) <= [SelectedCloseDate],
    1,
    0
)

Step-2: Add Open Date as a Slicer (Visual-Level Filter)

Use a slicer visual for OpenDate, and apply a visual-level filter: Show only values where FilterOpenDates = 1.

Advanced Option: Field Parameters or Bookmarks
If you're using Field Parameters or Bookmarks, you could create alternate views based on Close Date selections. However, this becomes complex and static.

Best Practice:

If you want truly dynamic control between slicers (like cascading filters), consider:

Creating a calculated table with pre-filtered Open Dates based on Close Date selection.
Or using Power BI Sync Slicers and visual filters smartly to guide users, even if not fully automatic.

If you found this solution helpful, please consider accepting it and giving it a kudos (Like) it’s greatly appreciated and helps others find the solution more easily.

Best Regards,
Nasif Azam

Re: slicers automatically selected based on another slicer selection

bharosha — Thu, 05 Jun 2025 19:25:21 GMT

Hi Nasif_Azam , I want to have open date slicers to be selected depending upon the values selected in close date. For an example, If I set close date year 2024- 2025, then I would open date year filter to automatically select all the years less than or equal to (min close date year), which is 2024 in this case.

Re: slicers automatically selected based on another slicer selection

Nasif_Azam — Thu, 05 Jun 2025 19:44:49 GMT

Thank you for the clarification from her feedback. Here’s how you can automatically filter the Open Date slicer to only show years less than or equal to the minimum selected Close Date year such as showing <= 2024 if the Close Date selection is 2024–2025.

Goal Recap

Close Date Slicer: User selects one or multiple years (e.g., 2024–2025).
Open Date Slicer: Should automatically filter to show years ≤ minimum selected Close Date year (i.e., 2024).

Power BI doesn’t support automatic slicer-to-slicer filtering like this natively, but here’s a dynamic and usable workaround using DAX and visuals.

Step 1: Create a Calendar Table

You’ll need a date table with a year column:

Calendar = CALENDAR(DATE(2000,1,1), DATE(2030,12,31))

Then add a Year column:

Year = YEAR('Calendar'[Date])

Step 2: Create Relationships

Do not connect Calendar to both Open Date and Close Date directly.
Instead, keep Open and Close date columns in your main fact table, say FactTable.

Step 3: Create a Measure to Capture Min Close Date Year

MinCloseYear =

CALCULATE(

MIN(YEAR('FactTable'[CloseDate])),

ALLSELECTED('FactTable'[CloseDate])

)

Step 4: Create a Filter Measure for Open Date Year

This measure will return 1 if the Open Date Year is ≤ Min Close Year:

ShowOpenYear =

IF(

'Calendar'[Year] <= [MinCloseYear],

)

Step 5: Use Calendar[Year] as the Open Date Slicer

Add a slicer using Calendar[Year].
Apply a visual-level filter on that slicer:

Where ShowOpenYear = 1.

This will ensure the Open Date Year slicer dynamically adjusts based on the Close Date filter.

Limitation

The Open Date slicer won’t have its values auto-selected (checkbox ticked), but it will only show valid options, which guides the user properly.

If you need checkboxes to be auto-ticked, that’s not currently supported in Power BI without using tricks like Bookmarks + DAX + Selection panes, which are harder to maintain.

If you found this solution helpful, please consider accepting it and giving it a kudos (Like) it’s greatly appreciated and helps others find the solution more easily.

Best Regards,
Nasif Azam

Re: slicers automatically selected based on another slicer selection

maruthisp — Fri, 06 Jun 2025 01:22:21 GMT

Hi bharosha,

As per my knowledge, Power BI doesn’t support automatic slicer syncing, but we can simulate it using below approach:

A shared DateTable
Dual relationships (active/inactive)
DAX measures with USERELATIONSHIP
Visual-level filters
This method keeps your dashboard dynamic and responsive to user selections.

I am attaching pbix file for your reference, please check it.
slicers automatically selected based on another slicer selection.pbix

Hope the above pbix solution may help you.

Please let me know if you have further questions.

If this reply helped solve your problem, please consider clicking "Accept as Solution" so others can benefit too. And if you found it useful, a quick "Kudos" is always appreciated, thanks!

Best Regards,

Maruthi

LinkedIn - http://www.linkedin.com/in/maruthi-siva-prasad/

X - Maruthi Siva Prasad - (@MaruthiSP) / X

Re: slicers automatically selected based on another slicer selection

bharosha — Fri, 06 Jun 2025 14:00:44 GMT

Thank you for your help. Both of these options do not help me with pre-selection. I found preselection slicer that I used and solve this issue.