Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

USERELATIONSHIP doesn't work

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. 

Need Help
  • AlB's avatar
    AlB
    5 years ago

    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 

     

7 Replies

  • AlB's avatar
    AlB
    Community Champion
    5 years ago

    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's avatar
      Anonymous
      Not applicable
      5 years ago

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

  • AlB's avatar
    AlB
    Community Champion
    5 years ago

    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's avatar
      Anonymous
      Not applicable
      5 years ago

      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's avatar
    AlB
    Community Champion
    5 years ago

    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's avatar
      Anonymous
      Not applicable
      5 years ago

      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.