Slicer question

[AlexanderPowBI]

Hello,

I have a quesiton regarding slicers & when a slicer slices another slicer. 

I have a quite complex data model that I can't really share, but to simplify and concentrate the question, i think the important is:

1. Date_dim > fact_sales < Location_dim

Page 1: Two slicers

1. Date - not synced with other pages

2. Location - synced with all pages in the report. 

What I want to achieve:

When user is on page 1, he/she will slice on location and date. If there has been no sales for given date period in some location, it should be excluded from location slicer and in turn, theis location shall be excluded from other report pages. However, the other report pages shall not be sliced by date, only by location. 

To clarify: I select 2020-01-01 - 2023-01-01. In this period, I have no sales in England which therefore will dissapear from the location slicer.  However, other report pages shall still be based on 2020-01-01 - today, but exclude England. 

When slicing the location slicer, it properly slices the other pages as expected.

However, when I use the date slicer what happens is:

1. Location slicer gets sliced in page 1 as expected. I have achieved this in two separate ways

a) by using bi-directional filtering from location to sales table. 

b) by using DAX and returning 1/0 if sales exist for given date period & location > put the measure as visual level filter

So far so god. 

2. When I go to other pages, the location table has not been sliced. So the date filter on the location slicer seems only to affect page one.

Anyone know about this and how to fix it?

[Anonymous]

HI @AlexanderPowBI,

AFAIK, the default slicer are work on the report page level.

If you want the filter effect on multiple report pages, I'd like to suggest you take a look at the sync slicer features which can sync slicer filter effect across different report pages:

Enable the Sync Slicers feature in Power BI visuals - Power BI | Microsoft Learn

Regards,

Xiaoxin Sheng

[AlexanderPowBI]

Hi,

Thank you for your answer. I am quite aware of the sync slicer feature; however, I think I was unclear with my question. Let me elaborate.

On page 1, I have:

a) Date slicer

b) Location slicer

Both the date slicer and location slicer slice my fact table.

I am also using the following DAX measure:

Slicer_Sales = INT(NOT ISEMPTY(fact_sales))

This, I have set as a visual level filter on the location slicer to only show items when DAX evaluates to 1.

The intention is:

1. User slices date
2. Date slices fact table
3. Location slicer only displays locations where facts exist during the sliced date
4. Location slicer can be used to slice fact table based on existing locations for the given date frame

This all works well on page 1.

On page 2:

• Location slicer - synced.
• However, I do not want to have a date slicer on page 2.

On page 2, what I want to happen is:

• Fact table is NOT filtered by date.
• Fact table should be filtered by location/locations. The locations to choose from should only be those within the sliced date frame from page one.

This is not happening. So the synced slicer is not really synced now.

Example:

For example, I have sales in Stockholm, Vienna, London, Berlin, Munich.

Let's say for Stockholm, there are only sales made in 2024. For the rest of the locations, there are sales every year.

Page one:

On page one, there will only be a map displaying locations where sales have been made, one date slicer, one location slicer.

A user goes into page one and decides to slice the date to include 2020-2023.

Location slicer is now updated thanks to the DAX measure, which will now only display the options: Vienna, London, Berlin, Munich - excluding Stockholm.

Page 2: Graphs

The graphs should now display data from sales in Vienna, London, Berlin, Munich and exclude Stockholm, BUT also include 2024.

Now, Stockholm does exist even though the location slicer is synced on the pages, and page 1 has a date set to 2020-2023.

Does this clarify what I am trying to achieve?

[Anonymous]

HI @AlexanderPowBI,

In fact, the sync slicer only syncs to source field which used in the slicer, it does not mean the date slicer will also affect the location field. (slicer not able to modify other filter selections)

BTW, have you considered to use the drill-through filter? It can bring filter items from the source to the target report pages but it not able to sync the target page selections back to the source page.

Set up drillthrough in Power BI reports - Power BI | Microsoft Learn

Regards,

Xiaoxin Sheng

[AlexanderPowBI]

Hi, thanks for your reply. 

I have decided to go in another direction as this was to complex scenario, so we can close this thread.