Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Join us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.

Reply
AhmadBakr
Advocate I
Advocate I

Filter precedence in DAX functions

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]
)
1 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!

View solution in original post

5 REPLIES 5
Anonymous
Not applicable

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.

@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
Advocate I
Advocate I

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?

@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
Not applicable

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.

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.