cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
perhund Frequent Visitor
Frequent Visitor

USERELATIONSHIP does not override active relationship

Hi.

 

I have created the following simple relationships between Sales, Employees, Countries and Continents, with every employee assigned a primary continent.

relations.PNG

To see the total sales of countries in an employee's primary continent, I have tried the following measure:

Amount (Continent) = CALCULATE(sum(Sales[Amount]), USERELATIONSHIP(Employees[Continent], Continents[Continent]))

This however, still uses the active relationship. Curiously, if I deactivate the relationship between Sales and Employees, the measure generates the desired result. This leads me to believe, that I might have misinterpreted the following snippet from the USERELATIONSHIP reference:

"... Even if the relationship is inactive, it will be used and overrides any other active relationships that might be present in the model ..."

The test file can be downloaded here: https://www.dropbox.com/s/qiwddfw32o3oz2b/test.pbix?dl=0

 

Any help to further my understanding of USERELATIONSHIP or a workaround for the problem would be greatly appreciated.

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: USERELATIONSHIP does not override active relationship

These are not simple relationships. You have created an ambiguous path between the continent table and the sales table. There are 2 paths when you use USERELATIONSHIP, which should the model use?  The documentation is referring to 2 relationships between the same tables. In your case you have relationships between different tables. 

 

It seems to me that you have a conflict. The q is “is it employee location or sales location that determines continent?”  If it can be both, then use 2 continent tables or columns. If only 1, then just use that and ignore the other. 



* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
2 REPLIES 2
Super User
Super User

Re: USERELATIONSHIP does not override active relationship

These are not simple relationships. You have created an ambiguous path between the continent table and the sales table. There are 2 paths when you use USERELATIONSHIP, which should the model use?  The documentation is referring to 2 relationships between the same tables. In your case you have relationships between different tables. 

 

It seems to me that you have a conflict. The q is “is it employee location or sales location that determines continent?”  If it can be both, then use 2 continent tables or columns. If only 1, then just use that and ignore the other. 



* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
perhund Frequent Visitor
Frequent Visitor

Re: USERELATIONSHIP does not override active relationship

Thanks Matt. The following was a key insight to me:

"The documentation is referring to 2 relationships between the same tables. In your case you have relationships between different tables. "

 

For the record, I eventually got around the ambiguity by not using a relationship at all. I learned that I could simply compare the two dimensions directly in the measure:

CALCULATE(sum(Sales[Amount]),ALL(Employees),Countries[Continent] in VALUES(Employees[Continent]))

result.PNG

Helpful resources

Announcements
Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Power BI Helps Homeless and Trouble Youth

Power BI Helps Homeless and Trouble Youth

We spoke with Power BI Super User, Greg Deckler, about his charity work

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 50 members 986 guests
Please welcome our newest community members: