Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi.
I have created the following simple relationships between Sales, Employees, Countries and Continents, with every employee assigned a primary continent.
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.
Solved! Go to 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.
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.
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]))
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
96 | |
77 | |
63 | |
55 |
User | Count |
---|---|
143 | |
109 | |
89 | |
84 | |
66 |