Get text values to display using an inactive relationship

[AndyM2023]

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 NAME	CONTACT NAME	CONTACT EMAIL	OWNER (USER) EMAIL
Opp 1	John Doe	J.D@company1.com	e.fudd@acme.inc
Opp 2	Joe Bloggs	J.B@company1.com	e.fudd@acme.inc
Opp 3	Jane Doe	JaneD@Company2.co.uk	b.wayne@acme.inc

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.

[rajendraongole1]

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!!