Calculate ignores outer Filter context

[Anonymous]

Hi There,

After searching "running total" and "filter context" I couldn't find something that matches my question (in a reasonable time).

My base Idea was to compute a running total in DAX. I have a fact table 

and a calendar table (marked as such)

The "is_this_year" flag marks the current year (2021) with 1, otherwise 0. So the dates start in early 2019.

The setup is as follows: I have a page-level filter on "is_this_year":

and I wrote the following Measure:

theMeasure = 
   VAR cur_max = MAX ( 'Calendar'[Date] )
   RETURN
      CALCULATE ( MIN ( 'Calendar'[Date] ), 'Calendar'[Date] <= cur_max )

Because of the Page-level filter I assument that I would get 1.1.2021 for every row but instead I get this:

This lookes like the page-level filter is ignored.

My reasoning here is that in the DAX generated in Power BI I get a filter on Calendar[is_this_year] which is essentially a table

is_this_year

1

In the measure itself I modify the filter context and add a filter on the [Date] Column of the calendar table. My understanding of filter contexts was that, because those filters are applied on different columns they should exist side by side, meaning we have the following filters in the final filter context:

• [is_this_year] = 1
• [Date] <= 5.5.2021 (for example)

So I would expect to get a row like this:

Date	The measure
05.05.2021	01.01.2021

But I we can see in the picture above I get 03.01.2019 as a measure value.

Can someone help me understand this?

example file here:

https://1drv.ms/u/s!AtFejN9ixXnChShZpu7MiEgoXVw5?e=ZCdERm

Thank You!

[Fowmy]

@Anonymous 

I modified your measure, DAX works on the column-based engine (VertiPaq) , you have set a report level filter but  inside your measure, you modify the context with calculate, so you need to apply the external filters

theMeasure = 
VAR cur_max = MAX ( 'Calendar'[Date] )
RETURN
    CALCULATE ( MIN ( 'Calendar'[Date] ),  'Calendar'[Date] <= cur_max, VALUES('Calendar'[is_this_year]) )

[Anonymous]

thanks for your answer. But I still do not grasp it completly. Shouldn't the report level filter be used to modify the context in DAX? In the end everything is put into a SUMMARIZECOLUMS and the report level filter is applied via a TREATAS.

So is my Measure / the CALCULATE not using that context?

Perhaps you could clarify that 🙂 

Thanks, Sven

[amitchandak]

@Anonymous , use filter. Because without filter in calculate, it as good as using .

theMeasure = VAR cur_max =
            MAXX (ALLSELECTED( 'Calendar'),[Date] )
        RETURN
            CALCULATE ( MIN ( 'Calendar'[Date] ), filter(ALLSELECTED('Calendar'), 'Calendar'[Date] <= cur_max ))

refer

http://dataap.org/blog/2019/04/22/difference-between-calculate-with-and-without-filter-expression/