Forum Discussion

AndyM2023's avatar
AndyM2023
New Member
3 years ago

Get text values to display using an inactive relationship

Hello

 

I'm very new to Power Bi and was wondering if anyone could help with a query I have around table relationships.

 

So I have three tables:

 

CONTACT   OPPORTUNITY      USER
Contact Id     Opportunity Owner ID   User ID
Contact Name   Opportunity Contact ID   User Name
Contact Email Opportunity Name     User Email

 

 

I have an active relationship between Contact > Contact ID and Opportunity > Opportunity Contact ID. (many to one)

There is an inactive relationship between User > User ID and Opportunity > Opportunity Owner ID (one to many)

The USER table also has an active relationship to a different table.

 

I'm trying to put together a table that will look something like this:

 

OPPORTUNITY NAMECONTACT NAMECONTACT EMAILOWNER (USER) EMAIL
Opp 1John Doe[email protected][email protected]
Opp 2Joe Bloggs[email protected][email protected]
Opp 3Jane Doe[email protected][email protected]

 

I'm having trouble pulling the User Email because there's no relationship available. I read you can use USERELATIONSHIP to use an inactive relationship, but this won't display the values as it has to be used in a CALCULATE function.

 

I've tried a couple of forum posts around this (using stuff like COUNT, MAX, etc) but they don't seem to work and just generates an error around multiple results when expecting a single result?

 

If anyone can help it would be greatly appreciated.

 

Thanks.

Need Help

1 Reply

  • rajendraongole1's avatar
    rajendraongole1
    Icon for Super User rankSuper User
    2 years ago

    Hi AndyM2023 - as per my understanding , you can create measure in the Opportunity table to fetch the User Email from the User table.

    Owner Email =
    CALCULATE(
    MAX(User[User Email]),
    USERELATIONSHIP(Opportunity[Opportunity Owner ID], User[User ID])
    )

     

    it works and able to pull in the User Email for each opportunity owner, even with an inactive relationship.

     

    Hope it helps

     

    Did I answer your question? Mark my post as a solution! This will help others on the forum!
    Appreciate your Kudos!!