Forum Discussion
Helper IVFilter precedence in DAX functions
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.
- 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.