What's the difference between these 2 queries?

[RandVac]

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.

[ThxAlot · Accepted solution]

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

[RandVac]

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.

[Jihwan_Kim]

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

[RandVac]

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>.