Results the FILTER function calculates seem off

Hi everyone,

I have two input data tables, Table1 and Table2. I’m trying to calculate Table2 from filtered Table1. I encountered the following cases:

Case A:


In this case, the result in Table3 is filtered according to the min value of Table2, and that is correct.

Case B:


But in this case, the result in Table3 is not filtered according to min value of the filter which is referenced to Table2. The min date in the filter is 05/10/2016, but Table 3 is cut by the date 03/10/2016. But it should be cut by the date 05/10/2016. Can anyone point where I went wrong in this case?

any chance you can upload some sample data? Easier to diagnose if we can see the data model.

Thanks,

Enterprise%20DNA%20Expert%20-%20Small

Yes I demo model would be more helpful here. Here’s a few thoughts though.

It seems that Table3 is a calculated table and calculated tables are static parts of a model. So their contents do not change based on selections or slicers.

Maybe you can use table expressions in measures to get dynamic selections. Or you can try this formula:

SUMX(FILTER(table1;table1[date]<=MIN(dates_min[date]));table1[A])

You should always be setting up your model with a standard date table like the below (Always do this)

Then for any calculations after that you should be using measures.

Ultimately what you are attempting to do here is not difficult I don’t believe, I think you’re just making it way harder than it actually is…maybe over thinking it a little bit.

Definitely work your way through the bottom two course. This will clear things up immensely and I confident you’ll understand what is happening in your calculations much better quite quickly.

Good luck with these.

Thanks
Sam