TL;DR up front:
A normal CALCULATE with REMOVEFILTERS that works as expected does weird things (impossible values) when there is a visual-level filter applied AND a slicer is used (as in, a value is deselected), but works fine with either on its own. What's going on?
I'm going to ask this question in general because I can't really figure out a way to make it specific without uploading a 200 MB file chock full of company secrets, so please bear with me...
Ultimately I'm trying to create a rather complicated measure for a pivot table that will return the average for the lowest (3rd) level if there is data, if there isn't it'll return the average for the category two levels up, and if that doesn't have any data either it'll do a regression based on all data. For example, you have sales volume by continent, country, and state, and if there weren't any sales in a particular state and country, return the average for the continent, and if there are no sales on that continent, do a regression. Simple enough, I know, but luckily that's not exactly the question I have. I could solve the greater problem if I could just get DAX to consistently ignore pivot levels...
As an initial step, I created a function that just returns the count of values/table rows for the top level, regardless of what level of the pivot it's on:
CALCULATE(COUNTA(Sales[Id])+0), REMOVEFILTERS(Sales[Country], Sales[State]))
So far so good. Put this into a normal pivot and it'll work just fine, but here comes the weird part:
I have visual-level filters on this pivot on some unrelated fields; nothing fancy, just data quality stuff, column_x > 0, column_y < 300, that sort of stuff. I also have a slicer set on the top level, "Continents" in the example. If I deselect a "continent" with the slicer (i.e. all-but-one), the values change and go weird - I have no idea what exactly they show, but they seem to revert to something close to (but not exactly!) an ordinary COUNTA, so they no longer return a count where there isn't data at the lowest pivot level, only where there is. If I select all on the slicer, it works again. If I delete the visual filters from the filter pane, it also works.
As for the values, I managed to find one that I could check manually, and at the lowest, everything-applied filter level a standard count returns 6. There are, indeed, 6 rows that match. The broken function returns 7. There is no combination of filters that could return 7 - remove any combination and you'd have to be in the hundreds. The only pattern I can see is that the CALCULATE function always returns at least 1 more than the normal COUNTA, but that's it.
Does anyone have some sort of deeper understanding of what REMOVEFILTERS is actually doing to cause this? I thought I understood: it removes a filter. I have the pivot, the values are being filtered by a) a slicer set to the pivot's top level, "Continent", b) unrelated visual filters on the filter pane, and c) the 3 levels of the pivot table itself. I want to keep a) and b), and I want to remove 2 of 3 in c). The measure above does that, except when the slicer is used. Why?!
As it turns out, this is a perfect example of auto-exist screwing everything up. I managed to solve the issue by turning the continent-country-state columns into three(!) "dim" tables, linking them to the "fact" table, and REMOVEFILTERS immediately started behaving. It's probable that one "dim" table with all three columns would have sufficed, but then the join must be done on a concatenated column (on both tables) which is so ugly I decided to just go with three and hide them.