DAX : Can anyone help me understand the difference between these two DAX codes that I have written?

[tvaishnav]

I have written these two set of code. Can anyone tell me in plain English how are these two evaluated. Second version gives me the desired answer. My understanding of second one is as follows:

SUMX opens the row context over the table produced as a output from FILTER. SUMX then evaluates VAL over each row the output table. CALCULATE removes date filter so that SUMX evaluates for the all dates available.

I am confused why first one isn't giving the same output? First DAX code evaluates as if there is not Removefilter being applied.

AP = CALCULATE(
                SUM('Net Cash Position'[Val])
                ,REMOVEFILTERS('Date')
                ,FILTER('Net Cash Position'
                        ,'Net Cash Position'[Metric] = "Accounts Payable"
                        )
                )

AP = CALCULATE(
                SUMX(
                    FILTER(
                        'Net Cash Position'
                        ,'Net Cash Position'[Metric] = "Accounts Payable"
                        )
                     ,'Net Cash Position'[Val]
                    )
                ,REMOVEFILTERS('Date')
                )

[johnt75]

In the first example REMOVEFILTERS is not being applied to the FILTER function - both it and FILTER are being applied only to the SUM function.

In the second example, because FILTER is inside the SUMX it is picking up the context which includes the REMOVEFILTERS.

In a CALCULATE function, any filters which are applied at the same level, like in the first example, are not applied to one another, they are merely added to the filter context which is used for the main calculation.

[tvaishnav]

Thank you for the response. That explanation does help but I have a follow up question regarding code sample 1.

---

@johnt75 wrote:

In the first example REMOVEFILTERS is not being applied to the FILTER function - both it and FILTER are being applied only to the SUM function.

In the second example, because FILTER is inside the SUMX it is picking up the context which includes the REMOVEFILTERS.

In a CALCULATE function, any filters which are applied at the same level, like in the first example, are not applied to one another, they are merely added to the filter context which is used for the main calculation.

---

Keeping this aspect of CALCULATE function in mind, in code sample 1, REMOVEFILTER will remove date filter and FILTER will obtain rows over which I want to do the summation. I know this interpretation of code is wrong. Can you tell me what is wrong here?

[johnt75]

FILTER will retrieve the rows matching "Accounts Payable" with the existing filter context in place, so any filters on the date table will also be active. It will therefore only return rows which match Accounts Payable and which match the filters on the date table.

When REMOVEFILTERS is then called, there's really nothing for it do as all the date filters have already been applied

[SpartaBI · Accepted solution]

@tvaishnav in the first one the filter:

                ,FILTER('Net Cash Position'
                        ,'Net Cash Position'[Metric] = "Accounts Payable"
                        )

Is evaluated before / regardless of the removefilters on the date while in the 2nd one it's evaluated after the removefilers on date applied.
Because you are calling the entire table in the filter it calls the entire expanded table and you will get different result in the 2 scenarios.
Try writing the 1st one like this:

AP = CALCULATE(
                SUM('Net Cash Position'[Val])
                ,REMOVEFILTERS('Date')
                ,'Net Cash Position'[Metric] = "Accounts Payable"
     )

Also let me know if this also gives you the right anser:

AP = CALCULATE(
                SUM('Net Cash Position'[Val])
                ,REMOVEFILTERS('Date')
                ,FILTER(VALUES('Net Cash Position'[Metric]),
                        ,'Net Cash Position'[Metric] = "Accounts Payable"
                        )
                )

Showcase Report – Contoso By SpartaBI



      

[tvaishnav]

@SpartaBI I will test this out and get back. I do have a follow up question but I want to play with your code first. Thank you for your time.

[SpartaBI]

@tvaishnav sure.

In case this one was solved please don't forget to accept my previuos message as a solution