Filter and CALCULATE filters

DAX DAX Calculations
1

image
Hi,

I am trying to understand how to put complex code together.

The last line of the code, Dates(date] etc, is this part of the CALCULATE filter.
Why did you introduce the FILTER function in the code when a filter already exist in CALCULATE.
Why did you not use the filter in the CALCULATE for both FILTERS. (allselected dates and Dates[date].

Thanks in advance.
Elizabeth

2

good question

here are a couple of links to for additional insight between filter and calculate

https://forum.enterprisedna.co/t/simple-and-advanced-filters-with-calculate-using-filter-function/4924

3

Hi @ElizabethTachjian

Every calculation we do in measure is changed by the context that we’re referring to in the measure.
The filter is generally use to cut the data as per the requirements. So here the filter context is demanding to have all the selected dates from date table and secondly we are calling the dates < max date (max selected date) and after this filters we want total sales.

I know dax is bit tricky to understand so would recommend you to go through the video shared by @C9411010.

And if you want to go further please refer these as well:

Thanks
Ankit

4

Hi @ElizabethTachjian

In your example, the filter function is used to define a table which is passed to the calculate function as a filter input. Rewriting the DAX to have the filter function on a single line makes it clearer that the entire filter function result is the only filter being used by the calculate function as a filter input.

Cumulative Sales =
IF (
    ISBLANK ( [Total Sales] ),
    BLANK (),
    CALCULATE (
        [Total Sales],
        FILTER ( ALLSELECTED ( Dates ), Dates[Date] <= MAX ( Dates[Date] ) )
    )
)

In terms of why the filter function is being used: This would be to ensure the ALLSELECTED function is used instead of an ALL function when defining the table to pass to calculate.

To explain further. If you had just written Dates[Date] <= MAX ( Dates[Date] ) as the filter input to calculate, this would be syntax sugar for FILTER ( ALL ( Dates[Date] ), Dates[Date] <= MAX ( Dates[Date] ) ) which is what the engine converts it to in behind the scenes. This would naturally give you a different result.

I hope this helps your understanding.

5

Hi @ElizabethTachjian, we’ve noticed that no response has been received from you since the 16th of May. We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the checkbox. Thanks!

6

Hi @ElizabethTachjian, a response on this post has been tagged as “Solution”. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the check box. Also, we’ve recently launched the Enterprise DNA Forum User Experience Survey, please feel free to answer it and give your insights on how we can further improve the Support forum. Thanks!