0

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! :)

m1nkeh
  • 1,337
  • 23
  • 45

1 Answers1

2

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

Marco Russo
  • 169
  • 3
  • of course it is, d'oh! ;) thanks for the response marco, the reason this question came up is because in another part of my model i have FactData --> DimCustomer <-- DimCustomerSecure and if i lock down the DimCustomerSecure table with =FALSE() i need to wrap a load of measures with the COUNTROWS(ALL(DimCustomerSecure)) section in order for the filtering to run correctly, or rather, ignore the filtering! i hope the AS team make tables and columns securable objects in some future AS release. – m1nkeh Nov 06 '14 at 09:48