THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

SQLBI - Marco Russo

SQLBI is a blog dedicated to building Business Intelligence solutions with SQL Server.
You can follow me on Twitter: @marcorus

Using USERELATIONSHIP function in Measures and Calculated Columns #dax #powerpivot #ssas

You can use the USERELATIONSHIP function in DAX to apply a non-active relationship in a particular DAX calculation. This approach is usually simple in a measure (just use USERELATIOSHIP in one of the filter arguments of CALCULATE) but as soon as you try using it in a calculated column, you can find several issues.

In the article USERELATIONSHIP in Calculated Columns on SQLBI web site I described how to correctly write USERELATIONSHIP in this scenario, considering both sides of the relationship. AS you will see, using LOOKUPVALUE instead of USERELATIONSHIP can simplify your DAX code when you access to a lookup table!

Published Tuesday, October 01, 2013 2:25 PM by Marco Russo (SQLBI)

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Tommy said:

Hi Marco,

Just wonder why your formula mentioned in the "Userelationship in Calculated Column" article, doesn't seem to facing the same issue as mentioned in http://javierguillen.wordpress.com/2012/03/05/userelationship-and-direction-of-context-propagation/

As what I can see from your illustration, you are placing the formula in fact table and not the lookup table, right?

November 18, 2013 4:33 AM
 

Marco Russo (SQLBI) said:

Tommy,

if you take a look at the formula I used in the article, I remove the existing extended filter by using ALL( Calendar ) in a CALCULATE statement that is applied to the internal CALCULATE that explicitly propagates the fact table into the filter context, so that extended filter uses the alternative relationship instead of the standard one.

Actually, this is a workaround to the issue described by Javier in his blog post.

Marco

November 19, 2013 5:31 PM
 

Tommy said:

Thanks Marco. That's a brilliant idea !

Perhaps Javier shall create a link from his blog to your article for workaround reference ;)

November 19, 2013 11:35 PM
 

Marco Russo (SQLBI) said:

Just added a link in his comments! :)

November 20, 2013 1:46 AM
 

Namrata said:

Hi,

I am working on a tabular cube.Here i have two date columns separation date and resignation date in my fact table .I have two measures ,one that counts the number of employees based on the separation date and one that counts based on resignation date.My DimDate dimension has an active relationship with separation date column and inactive relationship wid resignation date column.

When i access my cube through excel ,both the measures show proper values .But,when i double click on the cell of the measure containing the count of employees based on resignation date(inactive relationship),I get even those rows that i have filtered in my measure.for eg My measure is

ResignedEmployees :=Calculate(Count(EmpID),userelationship(Employee[ResignDate],DimDate[Date]),MyFlag="True")

This should ideally give me only those rows that have "Myflag" column =true,But the drill through seems to ingnore the filter expression of the calculate function and returns more than it is actually showing.This behaviour is really weird and i cannot think of why this could be happening.

Please guide as to what i am missing out here.

Thanks.

April 1, 2014 8:38 AM
 

Marco Russo (SQLBI) said:

Namrata, the drillthrough feature ignore any calculate/filter applied to the measure, and only display the initial filter context. This is a big issue also for YTD measures (you would only see the current selection, not all the days since the beginning of the year).

Unfortunately, there is no way to change the drillthrough behavior.

April 1, 2014 2:22 PM
 

Namrata said:

Hi Marco,

Thanks.Just to be clear,Drill-through ignores the filter context only in case of userelationship function i.e inactive relationships or does it ignore it in general regardless of the relationship????

Because in the latter case(active relationship),the drill-through seems to have always worked for me (i haven't checked for YTD measures though).

Is there any work around for this ,like creating another date dimension connecting to the column used for inactive relationship ??

April 2, 2014 1:50 AM
 

Marco Russo (SQLBI) said:

Namrata - as I said, drillthrough uses the initial filter context, regardless of the expression you have in the measure. So it ignores the *changes* you apply to filter context in your calculate functions.

April 2, 2014 2:07 AM
 

Namrata said:

Hi Marco,

I think i misunderstood initially,I was so stuck on that active-inactive relationship thingy in my mind.But now i get it as clear as day.

Thanks a lot. :)

April 2, 2014 2:49 AM

Leave a Comment

(required) 
(required) 
Submit

About Marco Russo (SQLBI)

Marco Russo is a consultant, writer and trainer specialized in Business Intelligence with Microsoft technologies. He runs the SQLBI.COM website, which is dedicated to distribute resources useful for BI developers, like Integration Services components, Analysis Services models, tools, technical information and so on. Marco is certified as MCT, MCDBA, MCSD.NET, MCSA, MCSE+I.

This Blog

Syndication

Archives

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement