Remove Filters Not working with Date Slicer

[WilliamHF]

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 · Accepted solution]

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....

[v-nuoc-msft]

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.

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

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.

Regards,

Nono Chen

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

[WilliamHF]

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_]

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.

[WilliamHF]

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.