THE SQL Server Blog Spot on the Web

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

SQLBI - Marco Russo

Please visit the same blog at - comments are now disabled here but you can comment on the new blog (all blog posts available here are available there, too).
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 1, 2013 2:25 PM by Marco Russo (SQLBI)



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

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:


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.


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:


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.


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:










"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:


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

September 10, 2014 10:50 AM

Connor said:


My model uses LOOKUPVALUE in place of USERELATIONSHIP.  However, when I deploy and query, I receive an error 'USERELATIONSHIP cannot be nested more than 10 times'.  

Is there a limit to the number of LOOKUPVALUE columns per table or model?  None of mine are nested, so this is a confusing error message.

October 21, 2014 11:43 AM

Marco Russo (SQLBI) said:

Connor, are you saying you are getting a USERELATIONSHIP error even if you are *not* using USERELATIONSHIP in your formula? If yes, can you repro it with a small sample file?

October 21, 2014 11:49 AM

Connor said:

Correct, I do not have a single USERELATIONSHIP formula in my model.  This problem only started once my model grew, so I do not think I can reproduce it in a sample file.

To your knowledge, is there a limit to the number of LOOKUPVALUE formulas per table or model?  I have 5-6 per table.  May not be ideal, but I have yet to see anything that mentions a limit or best practice.

Another post I found mentioned dropping the entire model and processing from scratch, as a workaround.  Is there something else I can do?

Here is a screenshot of the errors:

October 21, 2014 12:29 PM

Marco Russo (SQLBI) said:

Connor, which version of Power Pivot are you using? With Excel 2010 o 2013? Or are you using SSAS Tabular? Which Build number?

There could be a bug in error message displayed and the real error is another one, but latest build should work.



October 21, 2014 12:53 PM

Connor said:

I am working with SSAS Tabular 11.0.3 with SQL 2012 SP1.  As long as you are stumped too, then I guess it is okay!

October 21, 2014 1:15 PM

Marco Russo (SQLBI) said:

Can you try installing CU12 for SP1 (Build 11.00.3470)?

October 21, 2014 1:21 PM

Connor said:

Turns out it was a service pack and compatability mode issue.   You are the man Marco!

October 21, 2014 5:55 PM

Pedro said:


what would be the syntax if I need to combine both RELATED and USERELATIONSHIP? I need to bring the value from another table using related, but using a specific foreign key.

December 3, 2014 5:54 AM

Marco Russo (SQLBI) said:

Pedro, read the article linked in the blog post:

December 3, 2014 6:07 AM

Mejd said:

Hi Marco,

I'm trying to create a reference table in powerpivot. Here sits unique IDs, and I want to append calculated columns of aggregated values from other tables (eg. total visits from the visits table, total sales from the sales table) I use the calculate(sum(...), userelationship(col1,col2) for the total visits calculated column and it works fine - great. Though when I try the second column for sales I receive an error about a circular reference detected (the formulae are correct).

To check the formulae are correct I wipe blank the visits column and try the sales column, and it works. I guess this is to do with relationships. I have tried making inactive/active with the tables but to no avail.

Can userelationship() only be used once per related column?


September 1, 2015 7:38 AM

Mejd said:

Any luck?!

September 4, 2015 4:48 AM

Marco Russo (SQLBI) said:

Mejd, check this article for understanding the reasons (and workarounds) for circular references:

September 15, 2015 5:07 AM

TJ said:

Marco... How is the ROW IDENTIFIER set in Power Pivot (as per the Alberto example in Tabular)?

I also tried to resolve the issue using CALCULATETABLE(X,ALLEXCEPT(CalcY)) type solution, but got errors expecting a table reference...

My initial Calculation was:


I then want to add a 2nd Calculated Column of similar structure but using a different column relationship



Note the People_Country1 column relationship in #2.

This generates the circular dependency, as expected...but I cannot get ALLEXCEPT or CALCULATETABLE working in both the formulae to remove the dependency when the filter context is evaluated.

Can you provide a hint?

March 23, 2016 12:27 PM

Marco Russo (SQLBI) said:

The problem is the USERELATIONSHIP - why you don't use LOOKUPVALUE instead?

March 24, 2016 5:40 AM

TJ said:

Thanks Marco... yes, that works for 1 extra column (although performance I think will be poorer).

However, if I try to then add a 3rd and use LOOKUPVALUE again, I generate a circular dependency.

Is there a way round this?

March 24, 2016 8:50 AM

TJ said:

Even if I remove the first double-CALCULATE and put ONLY LOOKUPVALUE, I can get my 2 Countries. But if I apply that same logic to same table but lookup a Language, it generates a circular dependency.

I can't see how I can avoid this situation purely with DAX in Power Pivot

Thanks for your input

March 24, 2016 8:52 AM

TJ said:

Problem resolved Marco, if not ideal scenario.

The problem was related to a CALCULATE on the related table. I changed that to a LOOKUPVALUE also and am able to return the correct result.

I will look for a way to try to get ALLEXCEPT() to work so I can maintain performance benefit of using CALCULATE.

March 24, 2016 9:36 AM

Marco Russo (SQLBI) said:

Thanks for the feedback TJ!

April 16, 2016 2:41 PM

Simon said:

Hi Marco,

I am trying to do the following calculation using USERELATIONSHIP(). Is there any way, I can avoid the "+" and make the calculation more compact?



’Order’[Status]=’Complete’ || ’Order’[Status]=’Cancel’,






’Order’[Status]<>’Complete’ && ’Order’[Status]<>’Cancel’ ,



August 1, 2017 4:04 AM

Marco Russo (SQLBI) said:

No, you need two CALCULATE to use the two relationships.

August 4, 2017 2:18 AM
New Comments to this post are disabled

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



Privacy Statement