COALESCE breaks filtration

[Sokol]

Hi!

I received a task to update some existing reports due to internal changes in the structure of the company. From some point of time they will replace/rename/divide/union their zones in a new way. So they want to see zones that were active in a selected period of time.

I thought it would be a relatively easy task until I started testing new data. I added a table that shows which zone is active/nonactive in some year/month and connected it to the dataset.

But it seems that COALESCE function which is used heavily through reports breaks the filtration I set up.

For example in my test report -  zone 1 and zone 2 is active on 1 and 2 months and starting from month 3 they are deactivated. For zone 3 the situation is opposite. 

So I expect that the fact table should show only active zones, but it shows all 3 despite the selected month.

I'm trying to come up with some solution that would require as less modifications of existing DAXes as possible.

But it seems that I'm missing something obvious.

Please help!

[v-stephen-msft]

Hi @Sokol ,

I'm a little confused about the active type of zonId 3. In the data source is False, why is it True here?

Best Regards,

Stephen Tao

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

[Sokol]

Have the same question) 

It seems like COALESCE breaks relations. But how, and how to cope with it - that is what bothers me.