Forum Discussion
What's the difference between these 2 queries?
A small data model with two tables <Dfact> <Ddim>
<Ddim>[SubCategory] (1) --- (*) <Dfact>[SubCategory]
The first query returns 3.
Whereas the second query returns 1.
<Dfact>
| ID | SubCategory |
| 1 | SC1 |
| 2 | SC1 |
| 3 | SC2 |
| 4 | SC2 |
| 5 | SC3 |
| 6 | SC3 |
<Ddim>
| Category | SubCategory |
| C1 | SC1 |
| C1 | SC2 |
| C2 | SC3 |
| C2 | SC4 |
| C3 | SC5 |
EVALUATE
CALCULATETABLE (
{ COUNTROWS ( Ddim ) },
Dfact,
Dfact[SubCategory] = "SC1"
)
EVALUATE
SUMMARIZECOLUMNS (
Dfact,
FILTER ( ALL ( Dfact[SubCategory] ), Dfact[SubCategory] = "SC1" ),
"count", COUNTROWS ( Ddim )
)
What's the difference between the 2 quries?
Thanks in advance.
This is what happens under the hood for query 1,
For any CALCULATE() / CALCULATETABLE(), DAX engine evaluates all filter arguments ( 1.expanded table and 2.predicate in your case ) independently from each other before final evaluation.
More specifically,
1.expanded table
filters Ddim via [related C] and [related SC] (data lineage kept)
2.predicate
doesn't propagate the filtering to Ddim due to 1:* direction constraint by default.
An alternative to query 2 is
4 Replies
This is what happens under the hood for query 1,
For any CALCULATE() / CALCULATETABLE(), DAX engine evaluates all filter arguments ( 1.expanded table and 2.predicate in your case ) independently from each other before final evaluation.
More specifically,
1.expanded table
filters Ddim via [related C] and [related SC] (data lineage kept)
2.predicate
doesn't propagate the filtering to Ddim due to 1:* direction constraint by default.
An alternative to query 2 is
Many thanks!
Now I know what I misunderstood about the CALCULATE.
I mistakenly thought CALCULATE merges the filter arguments by intersection before applying to the data model as below:
The arguments are evaluated independently from each other in the CALCULATE, and they filter the corresponding columns separately before final evaluation.
Hi,
I am not sure what is your desired outcome, but regarding the first one,
it shows the same result if it is written like below.
And, having the condition like the below on many side of the relationship
Dfact[SubCategory] = "SC1"does not influence on counting rows in one side of the relationship.
I assume you want to see this result = 1, and please try something like below.
RELATEDTABLE function (DAX) - DAX | Microsoft Learn
Thank you!
I was wondering how CALCULATE merges the filter arguments and yes I was confused why it's not 1.
I mistakenly thought CALCULATE would apply the filter Dfact[SubCategory] = "SC1" to the first filter argument <Dfact>.
