Relation with multiple possibilities

[Aikeno]

Hello, 

I try to create something like this in Power BI.

I have two table (1 and 2) and i try to create a table like on the right of the picture.

I am able to dot it with only Champion name or Investigator name on my right table but im struggling to have both is it something possible ?

If yes, how can I achieve that ?

Thanks in advance !

[v-yiruan-msft]

Hi @Aikeno ,

You can create two calculated columns in the table 'Table 1' as below to get it, please find the details in the attachment.

Champion full name =
CALCULATE (
    MAX ( 'Table 2'[First name] ) & " "
        & MAX ( 'Table 2'[Last name] ),
    FILTER ( 'Table 2', 'Table 2'[User ID] = 'Table 1'[Champion ID] )
)

Investigator full name = 
CALCULATE (
    MAX ( 'Table 2'[First name] ) & " "
        & MAX ( 'Table 2'[Last name] ),
    FILTER ( 'Table 2', 'Table 2'[User ID] = 'Table 1'[Investigator ID] )
)

Best Regards

[Visharavana · Accepted solution]

Hi @Aikeno 
You can achieve this in two different ways.

Lookupvalue:

championname = lookupvalue('table2'[firstname],'table1'[championid],'table2'[userid])

investigatorname = lookupvalue('table2'[firstname],'table1'[investigatorid],'table2'[userid])

Userelationship:

Same as first method, you can create two relationship one is active and another one is inactive relationship.

see this link userelationship