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.