REMOVEFILTERS Confusing Behavior

[mertolocel]

Hello,

I came upon a case which got me super confused about the way DAX works. I'd appreciate it if you guys could take a look. 

Here is my model, there are 3 tables date, dim and fact as below : 

I was trying to come up with a measure that would count the rows in the fact table, with certain date filters from the fact table while completely ignoring the link between dim[Created Date] and date[Date Actual]. 

Starting off in very high confidence, I have written the following DAX and put it in a table with date[Year Month] : 

I got super confused seeing the result, because I was expecting the REMOVEFILTERS to remove the filters from the Created Date which was being passed on from the date[Year Month]. So, in Feb 24 I was only seeing tools with the Created Date = Feb 24.

Then I assumed that it was related to REMOVEFILTERS itself, so I've tried it with ALL( dim[Created Date] ), ALLSELECTED, ALLEXCEPT,  KEEPFILTERS and even CROSSFILTER( dim, date, NONE ). Nothing worked. I was always seeing the Feb 24 values for only the items with [Created On] = Feb 24. It looked like it was still filtering through that dim-date connection. 

At this point, I started questioning my DAX knowledge and came up with me missing something very fundamental. 

When I deactivate the relationship between dim and the date, it works exactly as intended as below.

I was able to solve this finally, by wrapping the fact table with an ALL so the Created On filter stopped flowing to my fact table and the measure worked : 

But this has been giving me sleepless nights, it has almost became an obsession.

I don't know how many pages of DAX questions I've read and how many ChatGPT prompts I've written, it always comes down to the usage of REMOVEFILTERS or ALL to ignore this relationship and see the visual as intended even with the relationship being active. 

Just wanted to see if anyone else had the confusion of this behavior and somehow find a way to understand it,

Thank you.

[mertolocel]

Oh my god man, I can die in peace now. Thank you so much. 

I love your content by the way, 

Cheers.

[amitchandak · Accepted solution]

@mertolocel , Use it in countx like this

CALCULATE(COUNTx(FILTER(Employee,Employee[Start Date]<=max('Table'[Date]) && (ISBLANK(Employee[End Date]) || Employee[End Date]>max('Table'[Date]))),(Employee[Employee Id ])),CROSSFILTER(Employee[Start Date],'Table'[Date],None))