Forum Discussion

RandVac's avatar
RandVac
Frequent Visitor
2 years ago
Solved

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.   ...
calcualtetable
expanded table
filter propagate
SUMMARIZECOLUMNS
  • ThxAlot's avatar
    ThxAlot
    2 years ago

    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

Jihwan_Kim's avatar
Jihwan_Kim
Icon for Super User rankSuper User
2 years ago

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's avatar
RandVac
Frequent Visitor
2 years ago

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