Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
perhund
New Member

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

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 an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

View solution in original post

2 REPLIES 2

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 an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.