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.
I need a simple USERELATIONSHIP formula to work and I have no idea why it doesn't.
Please find an example here:
There are 2 tables with one active and one non-active relationship.
I've checked datatypes, the correctness of key columns and everything is correct.
The formula looks correct as well:
There is a calculation Column that should give provide a different result for room 3811, but it doesn't.
I tried a number of changes and none did help.
Solved! Go to Solution.
@Anonymous
Tricky. You could do something like this:
CALCULATION =
CALCULATE (
CALCULATE ( DISTINCT ( 'DIM Rooms'[Used for] ), 'Room data' ),
USERELATIONSHIP ( 'Room data'[Room # ADJUSTED], 'DIM Rooms'[Room only] ),
ALL ( 'DIM Rooms' )
)
but it's quite complex. Best use LOOKUPVALUE:
CALCULATION V2 =
LOOKUPVALUE( 'DIM Rooms'[Used for] , 'DIM Rooms'[Room only], 'Room data'[Room # ADJUSTED])
Please mark the question solved when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
@Anonymous
Tricky. You could do something like this:
CALCULATION =
CALCULATE (
CALCULATE ( DISTINCT ( 'DIM Rooms'[Used for] ), 'Room data' ),
USERELATIONSHIP ( 'Room data'[Room # ADJUSTED], 'DIM Rooms'[Room only] ),
ALL ( 'DIM Rooms' )
)
but it's quite complex. Best use LOOKUPVALUE:
CALCULATION V2 =
LOOKUPVALUE( 'DIM Rooms'[Used for] , 'DIM Rooms'[Room only], 'Room data'[Room # ADJUSTED])
Please mark the question solved when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
Hi @AlB ,
Thank you for this!
Still, do you have an Idea why it might not work?
I'm trying to find a clue why this more complex formula suggested by you should be used.
Thank you.
@Anonymous
Check this out
https://www.sqlbi.com/articles/userelationship-in-calculated-columns/
Please mark the question solved when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
@Anonymous
What is the expected resutl and why?
Please mark the question solved when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
Hey @AlB ,
In the "Room Data" table, the column "CALCULATION" should give "QQQ" for the "Room # Adjusted" value equal to 3811.
At least that's my understanding 🙂
Hi @Anonymous
The link for the file requires logging into google. Can you remove the requirement? Or host the file somewhere else?
Please mark the question solved when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
I am really sorry about that. I removed any restrictions. It should work now.
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 |
---|---|
111 | |
94 | |
83 | |
66 | |
59 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |