Confused about row context propagation and CALCULATE context transition

[Anonymous]

Hi all,

Recently I am reading The Definitive Guide to DAX. And all the contexts are driving me crazy.

If we have a relation like this:

And a calculated column:

Product[SalesWithSUMX] = 
AVERAGEX(
     Customer,
     CALCULATE(SUM(Sales[SalesAmount]))
)

I know before the CALCULATE evaluates new filter argument(which is none in the above example), it first transitions the row context into filter context.

From what I learned about row context, if we want to access the columns in different table, we should use RELATED or RELATEDTABLE, but it is also said that all relationships need to be of the same type, which is either all one-to-many or many-to-one and all of them going in the same direction, otherwise the RELATED or RELATEDTABLE will not work.

So if we want to have customer and product table to be related through one-to-many and then many-to-one, with the intermediate bridge table 'Sales' in the middle, and we cannot use RELATEDTABLE, then how the row context propagates? How do we connect the product table with customer table? What is the row context before the CALCULATE doing the context transition?

Can any one please elaborates a little bit more?

Thanks in advance! 

[LaurentCouartou · Accepted solution]

When the CALCULATE function is evaluated, two row contexts are available: one from the Product table and, one from the Customer table.

Both are used to filter the Sales table before evaluating SUM( Sales[SalesAmount]).

However, the Product table does not filter the Customer table in this expression. The expression could have been written this way:

Product[SalesWithSUMX] = 
AVERAGEX(
     ALL(Customer),
     CALCULATE(SUM(Sales[SalesAmount]))
)

The formula reads as:

For each row in the Customer table, calculate SUM(Sales[Amount]) for:
- the customer in the current iteration
- the product for the current row in the Product table
Then, average the results.

That is why, there is no use for the RELATED or RELATEDTABLE functions. You would use RELATED, for example, if you wanted to add a calculated column in the Product table that relied on the Product Subcategory.

[MattAllington]

Filters automatically propagate from the one side to the many side. If you turn on bi directional filtering, you can get them to propagate the other way. But use caution.  Depending on the complexity of your model, sometime bidirectional filter propagation can cause circular errors. 

You are actually asking about one of the hardest topics in DAX. I learnt everything I know about many to many relationships from The Italians. This is a good place to start. http://www.sqlbi.com/tv/many-to-many-relationships-in-dax-3/

Also so look at my blog post here http://exceleratorbi.com.au/many-many-relationships-dax-explained/

I'm an Excel guy, so I try to explain it how Excel guys think

[minhvuong93]

@MattAllington

Dear Matt,

I am also using Excel and having the issue with this type of many to many relationship,

Could you please help me to take a look at my post - maybe you could have some suggestion,

I am looking a for a calculated measures but with a kind of complicated relationship

http://community.powerbi.com/t5/Desktop/HELP-Many-to-many-replationship-Sum-ignore-filter/m-p/155249...