Forum Discussion
Relation with multiple possibilities
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 !
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
2 Replies
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
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
