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
 

Matt Marshall said:

I am working with a tabular model originally developed in PowerPivot and then recreated in an SSAS Tabular Project and deployed to the SSAS server.  Models are identical and have an inactive relationship that works in PowerPivot but does not yield data (via either a DAX query against SSAS in SQL Server Management Studio and that same query in SSRS).

If I modify the USERELATIONSHIP clause to point at the active relationship data is returned (but not sliced as I want).  The query has no syntax errors, it just does not display data across the inactive dimension relationship.

Any ideas on what might be happening or how to debug?  Here is the DAX with the USERELATIONSHIP on the last line:

evaluate

(

summarize

(

'View_Patient_With_SVF',

'DimDate'[Year],

'DimDate'[DisplayMonth],

'DimDate'[WeekOfMonth],

'DimDate'[Date],

"Eligible", COUNT('View_Patient_With_SVF'[Pat_ID]),

"Notification Letter Sent", CALCULATE(COUNT('View_Patient_With_SVF'[Pat_ID]), FILTER('View_Patient_With_SVF', ([CompletionStatus_LookupID] = 1))),

"Undelivered", CALCULATE(COUNT('View_Patient_With_SVF'[Pat_ID]), FILTER('View_Patient_With_SVF', ([CompletionStatus_LookupID] = 2))),

"Completed", CALCULATE(COUNT('View_Patient_With_SVF'[Pat_ID]), FILTER('View_Patient_With_SVF', ([CompletionStatus_LookupID] = 3))),

"Annual", CALCULATE(COUNT('View_Patient_With_SVF'[Pat_ID]), USERELATIONSHIP('View_Patient_With_SVF'[Elig_Annual],'DimDate'[Date]))

)

)

September 9, 2014 6:15 PM
 

Marco Russo (SQLBI) said:

Matt,

I don't see particular syntax issues. Are you sure that the 'View_Patient_With_SVF'[Elig_Annual] column has values that match with the DimDate[Date] column? If yes, are you sure that given a selection of DimDate (for a specific date), such a date exists in some rows of the fact table?

For example, if 'View_Patient_With_SVF'[Elig_Annual] contains date and time, there will be no matches with DimDate, you should truncate the time.

September 10, 2014 12:53 AM
 

Matt Marshall said:

I have checked all the items you mentioned and don't see any issues.  What is puzzling is that the identical model built from the same data in Excel PowerPivot renders the inactive relationship correctly.  The tabular model hosted in SSAS does not have a strong UI at this point and the only way I know to do validation is to hand-write DAX in the MDX query window.

I'll do some more digging today and report back if I can get things working.  I am relatively new to tabular and this was the first time something did not work as expected.

Thank you for checking my DAX query.

September 10, 2014 10:44 AM
 

Marco Russo (SQLBI) said:

Consider using DAX Studio - you can run the same DAX Query on both Power Pivot and Tabular model

http://daxstudio.codeplex.com

September 10, 2014 10:50 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