Filter precedence in DAX functions

[AhmadBakr]

Greetings folks,

I was surprised that the two below definitions resulted in totally different results. The first (using FILTER and ALL) gave the correct result, while the second counted all the records and did not apply the condition coming from CALCULATE!

The only way I see this possible is that calculate worked inside out, so it applied the ALL, counted the records, then applied the condition, which will be useless because COUNTAX resulted in a scalar value already and there is no table to apply condition on. But if this explanation is true, then it defies the whole purpose of conditions in CALCULATE(!!!)

Any elaborations here, please?

Definition-1 (correct result)

OrdersRemTot = 

COUNTAX(

FILTER(

ALL(Open_PO),

Open_PO[Matched Amount SAR] <> Open_PO[Amount SAR]

),

Open_PO[PO_Ref]

) 

Definition-2 (wrong result)

OrdersRemTot = 

CALCULATE(

    COUNTAX(

        ALL(Open_PO),

        Open_PO[PO_Ref]

    ),

    Open_PO[Matched Amount SAR] <> Open_PO[Amount SAR]

)

[Anonymous]

Hi @AhmadBakr ,

Definition-1, FILTER is used to create a temporary table that includes only the rows where Open_PO[Matched Amount SAR] <> Open_PO[Amount SAR]. The COUNTAX function then counts the Open_PO[PO_Ref] in this filtered table. This approach ensures that the condition is applied before counting.

Definition-2, CALCULATE modifies the filter context by applying the condition Open_PO[Matched Amount SAR] <> Open_PO[Amount SAR] after the COUNTAX function has already evaluated the count of Open_PO[PO_Ref] on the entire table (due to ALL(Open_PO)). Since COUNTAX returns a scalar value, the condition applied by CALCULATE does not affect the result as intended.

So, in Definition-1, the FILTER function ensures that the condition is applied first, creating a subset of the table that meets the criteria. In Definition-2, CALCULATE applies the condition after the count is performed, which is why it doesn’t filter the rows correctly.

To achieve the correct result, you need to ensure that the filtering condition is applied before the counting operation, which is effectively done using FILTER in Definition-1.

Best Regards

Yilong Zhou

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

[AhmadBakr]

@Anonymous I guessed the same and explained it in my initial post. However this nullifies the operation of CALCULATE salltogether, if true.

On the otherhand, have a look on my followup post, I will call you and elaborate on it from there.

[AhmadBakr]

A worthy update: The below also worked:

OrdersRemTot =

CALCULATE(

    COUNTAX(

        Open_PO,

        Open_PO[PO_Ref]

    ),

    ALL(Open_PO),

    Open_PO[Matched Amount SAR] <> Open_PO[Amount SAR]

)

Which means it is always safer to keep any filteration of the tables in the condition part of CALCULATE and do not apply it inside the expression, I guess?

[AhmadBakr · Accepted solution]

@Anonymous 

For this case, the result is correct, and identical to the FILTER case. If we use the same logic, then also here COUNTAX will return a scalar value hence all conditions will be meaningless.

I guess the dynamics of applying the filters is that filters coming from CALCULATE conditions are applied, then the table is evaluated and the expression is calculated. In my failing case, the condition coming from CALCULATE was overpowered by the ALL wrapped around the table name in the COUNTAX, hence cleared the condition filter.

I hope my guessing is right!

[Anonymous]

Hi @AhmadBakr ,

I think the conclusions of your follow up post below the thread are completely correct and we are basically all in agreement on the conclusions of our discussion, so I think there is absolutely no problem with what you are saying!

Best Regards

Yilong Zhou

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