CODEWITHSUNDEEP
powerbidaxssas-tabular
Mark Wojciechowicz
Mark Wojciechowicz

Reputation: 4477

How to filter on M2M table in Dax

I am trying to filter on a table which has a M2M relationship with my fact table. The relationship looks like the following.

Fact --> Customer <-- CustomerBridge --> Market

Two way filtering is turned on in both of the relationships to the bridge table. I wish to return only a certain Market. Here is what the query looks like:

EVALUATE
FILTER (
    SUMMARIZE (
        Fact,
        'Market'[Market Name],
        "Sales", [Sum of Sales]
    ),
    'Market'[Market Name] = "My Market Name"
)

This gives the error:

The column 'Market Name' specified in the 'SUMMARIZE' function was not found in the input table.

How can I get this filter to work or Dax to recognize the relationship?

EDIT: adding a two way relationship between fact and customer did not help

Upvotes: 0

Views: 1383

Answers (1)

RADO
RADO

Reputation: 8148

For such model to work, you need to enable propagation of filter context from CustomerBridge to Customer. There are several ways of making it work: using bidirectional relations in the data model, or using DAX (CROSSFILTER, SUMMARIZE, or expanded table filtering).

The simplest solution in PowerBI is to use bidirectional relations. The only bidirectional connection you need is between "CustomerBridge" and "Customer". All other relations should be regular (1:M): enter image description here Then:

EVALUATE
ADDCOLUMNS (
    FILTER (
        VALUES ( Market[Market Name] ),
        Market[Market Name] = "My Market Name"
    ),
    "Sales", [Sum of Sales]
)

How it works:

  • VALUES creates a list of distinct market names from table "Market"
  • The list is filtered for the desired market names.
  • As a result, FILTER returns the smallest possible table, so the formula should be fast
  • Finally, ADDCOLUMS iterates over the table, and calculates sales for each market name.

The downside of this approach: bi-directional relations are dangerous and might have very subtle undesirable effects on your model such as unexpected changes in filter context and performance degradation. The best practice is to avoid bi-directional relations if possible.

A better approach is to use programmatic bi-directional filtering with CROSSFILTER. For example, this measure:

M2M Sale =
CALCULATE (
    [Sum of Sales],
    CROSSFILTER ( Customer[Customer_ID], CustomerBridge[Customer_ID], BOTH )
)

will work correctly without bi-directional relations. If you create a matrix for Sum of Sales with Market Name on the rows, you will see sale amounts by market name. The advantage of this approach is that it enables bidirectional filtering programmatically, only when needed and without unpredictable side effect.

The same result can be achieved with SUMMARIZE:

M2M Sale =
CALCULATE (
    [Sum of Sales],
    SUMMARIZE ( CustomerBridge, Customer[Customer_ID] )
)

How this formula works is a bit of an advanced topic, but it works well.

Finally, in the old days "expanded table filtering" approach was used:

M2M Sale =
CALCULATE (
    [Sum of Sales],
    CustomerBridge
)

Here, bidirectional filtering is enabled by the bridge table itself (how exactly it works is also an advanced topic). I love the elegance of this formula, but it's probably the slowest of all approaches and can degrade your model performance.

Upvotes: 2

Related Questions