Overriding slicers

A question I’ve received a couple of times recently is how to override slicer selections in Power BI visuals. Or more specifically, if the user selects a specific item in a slicer (or filter), how can I show data for other items a visual that may be related by my selection.

An example of this is a report showing a date slicer, where a user can select a specific date, but we want to display data for a range of dates before the selected date. This challenge is not the same as using a measure showing a single value that represents a range of dates; rather, we want to show more rows than what gets selected in the slicer.

In the image above, the 29th of December is selected in the slicer, and the table visual is showing more than just data for this specific date. In this case, the [Revenue (Dynamic)] measure is hard-coded to show values for the five days preceding the selected date.

The update image shows the 27th December selected and the table show shows a set of dates related to the slicer selection.

How to solve

The trick to getting this working is to create a copy of the column used in the slicer in another table. Then, use the cloned column in both the axis of your visual AND in the DAX for your calculated column.

The first step is to clone the column from the slicer. In my case, I used the following DAX to create a single-column calculated table.

Dummy Date Table = ALL('Date'[Date])

I then create a relationship from the cloned table to the FACT table as follows.

Once this is in place, I can use the [Date] column from the ‘Dummy Date Table’ on the axis of my visual. Initially, this will produce a row for every value in the column regardless of what selections are set in the slicer.

The final part of the puzzle is to create a measure that checks the selected value from the slicer, and only returns a value for dates from the ‘Dummy Date Table’ that fall inside a prescribed range.

Revenue (Dynamic) = 
VAR mySlicerDate = SELECTEDVALUE('Date'[Date])

RETURN 
    CALCULATE(
        SUM('Sales'[Revenue]),
        ALL('Date'),
        FILTER(
            'Dummy Date Table',
            'Dummy Date Table'[Date] >  mySlicerDate - 5 && 
            'Dummy Date Table'[Date] <= mySlicerDate
            )
        )

Once a measure gets added to a visual, the default behaviour is the axis will only show a row when there is a non-blank value. The DAX code from this measure produces a blank value for every date outside the hard-coded five-day range. This default behaviour can get overridden, but we don’t want to do this in our case.

If more measures need to be added to the visual, the same DAX pattern should apply to each measure.

The combination of the cloned column and dynamic measure works well in all kinds of visuals such as the bar chart shown in the video below.

In my case, I also added a “What-If” parameter allowing the user to select a date range using a slider dynamically.

This technique can apply to scenarios other than dates. I used a date column here for this example, but by changing the DAX in the measure – you can use filters to find and show other rows of data that have a relationship to the value selected in the slicer.

The PBIX used in this example can be downloaded here.

As always, I’d love to hear from you if you find this technique useful and successfully apply it in your models.

Leave a Reply

  Subscribe  
Notify of
rimthorn
Guest
rimthorn

Thanks!

Anonymous
Guest
Anonymous

Or you just add a “GoNoGo” var.
EG : if((MyDate) <= SelectedValue(MyDate),1,0)
After that you just add the var in the filter pane concerned… Hope it will help 🙂

Frank Tonsen
Guest
Frank Tonsen

For me this is easier to understand using a disconnected table (‘Dummy Date Table’) for the slicer. Revenue (Dynamic) = VAR mySlicerDate = SELECTEDVALUE(‘Dummy Date Table'[Date]) RETURN CALCULATE( SUM(‘Sales'[Revenue]); FILTER( ‘Date’; ‘Date'[Date] > mySlicerDate – [Date Range Value] && ‘Date'[Date] <= mySlicerDate ) ) Loading...

Claus
Guest
Claus

It is not possible to download the report. Please show me how to get it.

trackback

[…] Overriding slicers […]

gravengerArur
Guest

Hello,
There always multiple ways to skin the cat ;). I wouldn’t create additional “DateTable” for the simplicity reasons, but I would remove interaction between slicer and table. Then create a measure which would be filtering the matrix (using selected variable in the slicer) – so then would be interaction, but more indirect.

Tijs
Guest
Tijs

Hi gravengerArur, please show us the dax for this. Because I think it’s not possible with DAX. You need an extra helper table, like this article explains. But I am really intereseted in your solution. So please share the DAX with us! Thanks in advance.