Question

How do I create a calculation that applies different filters based on a user-selected slicer option?
I need a Power BI measure that changes the applied filters dynamically based on a slicer selection. For example, if the user selects "Current Year," the measure should filter data for the current year, and if they select "Last Year," it should filter for the previous year. What is the best way to implement this logic using DAX?

Answer 1

Disconnected Table with DAX Measure using SWITCH() and SELECTEDVALUE() will help you achieve this.

1. Slicer Table:

Create an empty table with just a single column named ''Time Periods.'' Within its rows, insert the following values:

Current Year

Last Year

Last 3 Years

Last 5 Years

Again, this doesn't relate to the data model in your report.

2. Create a DAX Measure

Use SWITCH() For the selected filter:

SalesFiltered =
VAR SelectedPeriod = SELECTEDVALUE('Time Periods'[Period Name])
RETURN
SWITCH(
    SelectedPeriod,
    "Current Year", CALCULATE([Total Sales], YEAR('Sales'[Order Date]) = YEAR(TODAY())),
    "Last Year", CALCULATE([Total Sales], YEAR('Sales'[Order Date]) = YEAR(TODAY()) - 1),
    "Last 3 Years", CALCULATE([Total Sales], 'Sales'[Order Date] >= DATE(YEAR(TODAY()) - 3, 1, 1)),
    [Total Sales]  -- Default case
)

3. Add Slicer & Use Measure

• Add the Time Periods table as a slicer.

• Use SalesFiltered in visuals.