Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
a1b1c1
Advocate I
Advocate I

Filter a Slicer based on another Slicer (by placing a measure in Visual Level Filters)

So, I saw RADACAD's video which describes how to make different dimensions' slicers filter each other using a measure which is then placed in second slicer's visual level filter pane. Pretty neat stuff!

But now I want to take it up a notch. 
Here's my scenario:

I have placed two slicers based on the same date dimension. 
First slicer: 

a1b1c1_0-1605792751172.png

This slicer is an "on or before" date slicer. So, it is giving all the dates before 09-15-2020.

ISSUE: I need this slicer to filter my other slicer (from the same dimension) to show values only of the current selected month (in this case, that slicer should show dates from 09-01-2020 to 09-15-2020)

I have created a measure which I place in the visual level filter of my second slicer, which I think should work but isn't working somehow. Here it is: 

Date Filter = CALCULATE(
                          SUMX('Date Dimension',
                               IF('Date Dimension'[Date]>=DATE([Selected_Year],[Selected_Month],1), 1, 0)))
It should restrict the values from 09-01-2020 to 09-15-2020 but still returns all the dates <= 09-15-2020.

Any help? @Greg_Deckler @MFelix @amitchandak @mahoneypat @AlB @Fowmy 

 

 

1 ACCEPTED SOLUTION
a1b1c1
Advocate I
Advocate I

Okay, so I managed to solve my case.
Since I wanted to apply the Filtered Slicer on a visual to only show me the filtered values' data (which was not working as discussed above), I removed the second slicer altogether and placed the 'Date Filter' measure directly into the visual level filters of the visual. That worked instantly!
No need to use a second filter which would filter my data based on the selection of first slicer. 

 

a1b1c1_0-1605863116375.png

 

View solution in original post

3 REPLIES 3
a1b1c1
Advocate I
Advocate I

Okay, so I managed to solve my case.
Since I wanted to apply the Filtered Slicer on a visual to only show me the filtered values' data (which was not working as discussed above), I removed the second slicer altogether and placed the 'Date Filter' measure directly into the visual level filters of the visual. That worked instantly!
No need to use a second filter which would filter my data based on the selection of first slicer. 

 

a1b1c1_0-1605863116375.png

 

a1b1c1
Advocate I
Advocate I

@MFelix You're right. I gave it another try by adding a new date dimension and applying my measure onto it and it worked! 

Updated Measure: 


Date Filter = CALCULATE(SUMX('Date Filter Table',
IF('Date Filter Table'[Date]>=DATE([Selected_Year],[Selected_Month],1)
&& 'Date Filter Table'[Date]<=DATE([Selected_Year],[Selected_Month],[Max Day Selected]),1,0)))

INTERESTING THING TO NOTE: I was trying to apply the new filtered slicer on one specific visual only and I naturally wanted to "select all" resultant filtered values.
 a1b1c1_0-1605854037167.png

 

As you can see, the Filtered Dates are showing all the dates even though the Filtered Slicer is applied to it. Power BI strikes again. 
The reason for that is since I have chosen "Select All", it selects all the values of the slicer even if they are not appearing in it (CRAZY!). The measure which I have used for filtering only "hides" unwanted values and not removes them from the slicer. Now, my only option left is to manually select the filtered dates which is far from the solution I was going for.  

MFelix
Super User
Super User

Hi @a1b1c1 ,

 

The video that you refer is using two slicers from two different tables that are indirectly related by the fact table that allows to make the interaction between both slicers.

 

On your test what I can understand is that you have two slicer based on the same dimension table this will not work has you need since you are getting a calendar date and making a filter and then getting the same dataset and applying a calculation that will return the same subset.

 

The video is based on two different tables that what you also need to do have a first slicer and then using that make a filtering of other table using the subset on the first slicer.


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.