topic Re: What's the difference between these 2 queries? in DAX Commands and Tips

What's the difference between these 2 queries?

RandVac — Sun, 10 Dec 2023 06:20:33 GMT

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.

Re: What's the difference between these 2 queries?

Jihwan_Kim — Sun, 10 Dec 2023 08:18:45 GMT

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.

Re: What's the difference between these 2 queries?

ThxAlot — Sun, 10 Dec 2023 12:49:07 GMT

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

Re: What's the difference between these 2 queries?

RandVac — Sun, 10 Dec 2023 15:02:59 GMT

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.

Re: What's the difference between these 2 queries?

RandVac — Sun, 10 Dec 2023 15:09:33 GMT

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