USERELATIONSHIP doesn't work

[Anonymous]

I need a simple USERELATIONSHIP formula to work and I have no idea why it doesn't.

Please find an example here:

report

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:

CALCULATION = CALCULATE(SELECTEDVALUE('DIM Rooms'[Used for]),USERELATIONSHIP('Room data'[Room # ADJUSTED],'DIM Rooms'[Room only]))

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. 

[AlB · Accepted 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]

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.

[AlB]

@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 

[AlB]

@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 

[Anonymous]

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 🙂

[AlB]

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 

[Anonymous]

I am really sorry about that. I removed any restrictions. It should work now.