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

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

Reply
ctmullins
Advocate II
Advocate II

USERELATIONSHIP() applied to entire report?

Hi guys!  Relative newbie here, been working with PBI for the last six months or so, previously coming from a Qlik background.

 

I have a problem that I'm stumped trying to resolve.  I've scoured the various forums and blogs looking for a possible answer, but haven't found anything yet.

 

I have a star-schema data model with a date table that has two possible relationships to my fact table.  One relationship is marked as active, and the other inactive.

 

I've learned how to use USERELATIONSHIP() in my measures to show aggregated scalar values correctly.  But my issue is that there doesn't appear to be any way to deploy USERELATIONSHIP() to globally apply to all visuals throughout the entire report.  Essentially, I want one toggle button/filter to trigger a global USERELATIONSHIP() at the data model level, with the entire report updating appropriately.

 

ctmullins_0-1650385239314.png

 

Right now my toggle is built as a filter, but I'm not married to that if there's a better way (button? slicer?).

 

ctmullins_1-1650385323300.png

 

And here's what my (correctly functioning) Measures look like:

 

Quantity A = CALCULATE(Sum('Revenue Fact'[TotalQuantityNBR]), USERELATIONSHIP('Revenue Fact'[ActivityDateNBR],'Calendar'[DateNBR]))
 
Quantity S = CALCULATE(Sum('Revenue Fact'[TotalQuantityNBR]), USERELATIONSHIP('Revenue Fact'[ServiceDateNBR],'Calendar'[DateNBR]))
 
Total Charge Quantity = SWITCH(SELECTEDVALUE('_Date Toggle'[Code])
, "A", [Quantity A], "S", [Quantity S])
 
But this approach does not work when showing the row-level detail in a Table visual (for instance).
 
I simply cannot figure out how to globally enable the alternate relationship when the user requests it.
 
Anybody else tried to solve this?  Is there a way to request this as future functionality?
 
Thanks!
1 ACCEPTED SOLUTION

well, you have a lot of measures that return specific values, in case there is only 1 possible scalar value, for example: SELECTEDVALUE(). Whatever tooltip you are using, if it direct to a visual in Power BI that you have put there implicit measures, you can replace them with explicit measures, and than the userelationship will kick in  🙂
You can't do that without a measure, you can't change globally the active relatioship. It's only per measure or group of measures with use of calculation groups

View solution in original post

4 REPLIES 4
ctmullins
Advocate II
Advocate II

Thanks for the quick replies!  But - how do I use USERELATIONSHIP() without an aggregation/measure?  My report has a "Detail" page that uses a Table visual to display all of the rows from the Fact table that meet my filter criteria.  This needs to be row-level detail, not aggregated by dimensions - which means I can't use measures.  But I still need for all of the filter criteria to apply, including which date relationship to use.

 

Perhaps there's a way to construct a measure that doesn't do any aggregation, but simply returns a specific cell (row/column) from the Fact table?

well, you have a lot of measures that return specific values, in case there is only 1 possible scalar value, for example: SELECTEDVALUE(). Whatever tooltip you are using, if it direct to a visual in Power BI that you have put there implicit measures, you can replace them with explicit measures, and than the userelationship will kick in  🙂
You can't do that without a measure, you can't change globally the active relatioship. It's only per measure or group of measures with use of calculation groups

SpartaBI
Community Champion
Community Champion

Not sure exactly what you meant by globally, but if you meant you want the option of a toggle that will effect multiple measures at once you can use Calculation Groups.
You will have 2 calculation items:
1. SELECTEDMESURE()
2. CALCULATE(SELECTEDMESURE(), USERELATIONSHIP(...) )

You can also wrap up CI no 2 to only affect / not affect specific measures with an IF condition.

Hope that helps

amitchandak
Super User
Super User

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

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