Force RELATED() to take into consideration a certain relationship from the existing ones

[adriansuteu]

I have extracted data from my CRM with PowerBI and I have the following relationships between tables Leads an Users:

LEADS:                              USERS

CreatedBy (Userid)            *--1    Id

ModifiedBy (Userid)          *--1     Id

Owner (Userid)                 *--1     Id

I want to create a vizualization that shows the number of Leads / Owner NAME.

But If I create a new column in the Leads table with the formula OwnerName = concatenate(related(User[FirstName]),concatenate(" ",related(User[LastName]))) and I use OwnerName as axis it takes into consideration the first relationship (based on CreatedBy column. How do I get it to take into consideration the Owner column?

Thanks

[v-shex-msft · Accepted solution]

Hi @adriansuteu,

According to your description, you want to get the related user name based on owner, right?

If this is a case, you can refer to below formulas to get related user name.

Calculate column:

USERELATIONSHIP Function:
Owner Name = CALCULATE(CALCULATE(VALUES(User[Last Name])&" "&VALUES(User[First Name]),LEADS),USERELATIONSHIP(LEADS[Owner],User[User ID]),ALL(User))

LOOKUPVALUE Function:
Owen Name 2 = LOOKUPVALUE(User[Last Name],User[User ID],LEADS[Owner])&" "& LOOKUPVALUE(User[First Name],User[User ID],LEADS[Owner]) 

Result:

Regards,

Xiaoxin Sheng