Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin 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.
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)
Solved! Go to 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!
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.
A worthy update: The below also worked:
@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!
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.
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
11 | |
9 | |
8 | |
8 | |
8 |
User | Count |
---|---|
13 | |
12 | |
11 | |
10 | |
9 |