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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
WilliamHF
Advocate I
Advocate I

Remove Filters Not working with Date Slicer

I feel like I'm doing something so mind-numbingly simple that is almost has to work, and yet it isn't. I'm simply trying to see values for a basic formula extended past a simple date slicer. See snip below. I want my table to show "12" for every single month--in other words, I want "removefilters" to extend the formula to dates beyond the last date on the slicer.

 

Note: I have turned off the AutoDate file in Options, and just made my own calendar table (see snip below first snip).

 

What am I doing wrong??

 

Any help is much appreciated. I've read other blog posts but can't seem to find any direct help on this.

 

WilliamHF_0-1713387237316.png

WilliamHF_1-1713387309728.png

 

 

1 ACCEPTED SOLUTION
WilliamHF
Advocate I
Advocate I

Ok, found the issue. It's called Auto Exist, which I don't know anything about. But since I was working within my Calendar table, it wasn't working. When I made a second small table with the month names and connected it to my Calendar table, then it worked!

 

Of course, then I found this PowerBI post here, that I wish I'd seen earlier: https://community.fabric.microsoft.com/t5/Desktop/DAX-REMOVEFILTERS-Issues/td-p/2834453#:~:text=If%2....

 

 

WilliamHF_0-1713455907948.png

 

View solution in original post

5 REPLIES 5
WilliamHF
Advocate I
Advocate I

Ok, found the issue. It's called Auto Exist, which I don't know anything about. But since I was working within my Calendar table, it wasn't working. When I made a second small table with the month names and connected it to my Calendar table, then it worked!

 

Of course, then I found this PowerBI post here, that I wish I'd seen earlier: https://community.fabric.microsoft.com/t5/Desktop/DAX-REMOVEFILTERS-Issues/td-p/2834453#:~:text=If%2....

 

 

WilliamHF_0-1713455907948.png

 

v-nuoc-msft
Community Support
Community Support

Hi @WilliamHF 

 

@Wilson_ Thank you very much for your prompt reply.

 

Here allow me to explain to you the reason for the blank values in the matrix.

 

Since you used the slicer to intercept the dates from 1/1/2020 to 3/31/2024. Therefore, the 2024 matrix will only have data from January to March. Therefore, MEASURE will not be populated with dates from April 2024 onwards.

 

I hope I have explained this clearly to you.

 

If you want the selection in the slicer not to affect the presentation of the matrix, here I provide a way to do it:

 

Click on the slicer and go to the "Format" tab. Select "Edit interactions" and click on the circle in the upper right corner of the matrix view.

 

vnuocmsft_0-1713419573947.png

 

Incidentally, clicking on the graph on the left restores the slicer filtering. As shown below:

 

vnuocmsft_1-1713419948324.png

 

In the meantime, I have made changes to your code.

 

Measure = CALCULATE(MAX('Calendar'[Sort Month]), ALL('Calendar'))

 

To prove that the slicer is working, I similarly created a MEASURE to show the maximum date under the action of the slicer.

 

Max Date = MAX('Calendar'[Date])

 

 Here is the result.

 

vnuocmsft_3-1713420226476.png

 

Regards,

Nono Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

 

Hi, thanks for the input. I know about turning off the interaction between slicers. But shouldn't 'Removefilters' (or 'All') be able to override a date slicer? I thought that was the whole point of those 'removefilters' functions--that they remove all outside filters.

Wilson_
Memorable Member
Memorable Member

Hi William,

 

Firstly, I don't know where you heard that sorting your columns may mess with REMOVEFILTERS but I've never seen that happen and cannot think of any reason why it would.

 

Secondly, there will always be twelve months in a year so why do you need a measure at all instead of just using 12 wherever you would use this measure?


----------------------------------
If this post helps, please consider accepting it as the solution to help other members find it quickly. Also, don't forget to hit that thumbs up and subscribe! (Oh, uh, wrong platform?)

 

P.S. Need a more in-depth consultation for your Power BI data modeling or DAX issues? Feel free to hire me on Upwork or DM me directly on here! I would love to clear up your Power BI headaches.

Thanks for the input, Wilson! As far as the sort order, I agree it seems unlikely, but this article here https://www.sqlbi.com/articles/side-effects-in-dax-of-the-sort-by-column-setting/ made me a bit suspicious so I thought I would at least remove that possibility.

 

As to the 12 months--my actual formula is taking the month number (Max of month number). I just did the sample formula as a simple test. But probably should have clarified a bit.

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

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