# USERELATIONSHIP and direction of context propagation

March 5, 2012 6 Comments

My friend Hrvoje Piasevoli ( twitter ) pointed out to me recently that when using USERELATIONSHIP in a calculated column, the output was not the expected value based on the selected relationship. I have used USERELATIONSHIP successfully in DAX measures a good number of times so I was surprised to hear the news.

In my effort to replicate the issue so I could better understand it, I used a calculated column on a BISM Tabular model based on the Adventure Works database. The expression, on the FactInternetSales table, was as follows:

CALCULATE( VALUES(DimDate[DateKey] ) ,

USERELATIONSHIP( FactInternetSales[ShipDateKey], DimDate[DateKey] )

)

When using these tables in a Tabular model, the default ‘active’ relationship on this table is based on Order Date:

Clearly, the output I expected was the [DateKey] value associated with the current row through the relationship defined using the [ShipDateKey]. In other words, I expected the output to be the same as what the row showed for Ship Date.

Interestingly, the value I got back completely ignored the USERELATIONSHIP and was based on the active relationship using Order Date:

So is it that USERELATIONSHIPS are only supported on Measures and not Calculated Columns? Not quite. The problem is not with the type of DAX expression but with **the direction of context propagation**. As an example, lets examine the following DAX query on the same Tabular model (click the image to expand it):

ADDCOLUMNS is the equivalent expression to adding a Calculated Column in the Grid pane of BISM Tabular or PowerPivot. And as you can see, neither of the expressions using USERELATIONSHIP returned the expected value. The only expression that returned the correct Ship Date was the one using a *calculated relationship* based on FILTER, which did not leverage the model’s defined relationships.

However, if we reverse the expression and evaluate it from the lookup table (DimDate), we get:

In other words, from the lookup table to the base table, USERELATIONSHIP works just fine. The issue only emerges when invoking the function from the base table and making a call into the lookup table.

In DAX, context is automatically propagated from the lookup table to the base table. In other words, when you assign filter context on a lookup table, only the corresponding base table rows are activated and taken into account when resolving the expression. I described this on a prior blog entry related to Tabular relationships (Point # 2 here.) Propagating context in the other direction does not happen automatically, and one must use functions like CALCULATE or RELATEDTABLE to generate this effect.

In this case, though, even using CALCULATE is not sufficient to propagate the context appropriately and one must use a **calculated relationship**. In general terms, this should not be very problematic. However, the expression cannot benefit from the superior query performance delivered by leveraging the model’s defined relationships.

So in summary, USERELATIONSHIP can be used in both, Measures and Calculated Columns. However – be mindful of the relationship direction as context will only respect USERELATIONSHIP when the filter is generated from the lookup table. Use a calculated relationship if you must place the filter on the base table.

Thanks Javier, nice explanation

Javier, thanks for this very interesting article. Both this post and the one from January 27th 2012 explore some fundamental workings of relationships in DAX & Tabular models.

Before we get started on why the USERELATIONSHIP filter expression doesn’t work in a column in the fact (base) table, I think we need to explain why CALCULATE works altogether from the fact table. We already know that CALCULATE converts row context into filter context. But we also know that filter context is not propagated from the fact table to the dim (lookup) table. So why does CALCULATE(VALUES(DimDate[DateKey])) in the fact table return the [DateKey] that corresponds to the Order Date on that row? Shouldn’t it return all the dates in DImDate? (and then we’d get some error about multiple values…)

Hi ruve1k,

When using CALCULATE, context propagates via ‘extended tables’ that go from the fact to the dimension(lookup) through a left outer join-type of relationship (See Jeffrey Wang article on DAX cross filtering http://mdxdax.blogspot.com/2011/03/logic-behind-magic-of-dax-cross-table.html ). When invoking CALCULATE from the fact table itself, context is able to reach out to the [DateKey] column as it belongs to its extended version.

There is an apparent discrepancy in not being able to leverage the same method when USERELATIONSHIP is used. I believe this is because extended tables (in play during the use of CALCULATE) are constructed using ‘active’ relationships in the model.only. I am not yet sure if this is a bug of is by-design behavior.

Pingback: DAX context propagation, inactive relationships and calculated columns | Javier Guillén

Javier,

thanks to a comment I received in my blog post (http://sqlblog.com/blogs/marco_russo/archive/2013/10/01/using-userelationship-function-in-measures-and-calculated-columns-dax-powerpivot-ssas.aspx), I remembered that a workaround to this issue is described in this article: http://www.sqlbi.com/articles/userelationship-in-calculated-columns

Indeed. Thanks Marco for posting the link to the workaround!