CODEWITHSUNDEEP
sql-serverssasssas-tabular
m1nkeh
m1nkeh

Reputation: 1397

Tabular Model Many 2 Many

Can anyone explain to me what exactly is going on behind the scenes with the following two queries? they seem to exhibit the same results, but which is "better" for filtering a measure in tabular model dax across a many-to-many relationship...

Here is the (pretty standard) model: FactData ---> Account <--- AccountCustomerM2M ---> Customer

Example 1:

SumAmountM2M - v1 :=
IF (
    COUNTROWS ( ALL ( Customers ) ) > 0,
    CALCULATE ( SUM ( 'FactData'[Amount] ), AccountCustomerM2M ),
    SUM ( 'FactData'[Amount] )
)

Example 2:

SumAmountM2M - v2 :=
IF (
    ISCROSSFILTERED ( 'Customers'[CustomerKey] ),
    CALCULATE ( SUM ( 'FactData'[Amount] ), AccountCustomerM2M ),
    SUM ( 'FactData'[Amount] )
)

Thanks for your help! :)

Upvotes: 0

Views: 557

Answers (1)

Marco Russo
Marco Russo

Reputation: 179

The example 1 is always using the many-to-many calculation regardless of the selection of Customers, unless the Customers table is empty. Maybe you wanted to write IF ( COUNTROWS ( ALL ( Customers ) ) > COUNTROWS ( Customers ), ...

The example 2 executes the many-to-many calculation only whether you have a direct or indirect selection of customers (e.g. you selected customer's city in a slicer)

Example 2 optimize the calculation because the ISCROSSFILTERED function only executes once in the query plan and is more optimized than COUNTROWS approach, which in a complex iteration might be more expensive because executed in every row context of an external iteration.

Marco

Upvotes: 2

Related Questions