Forum Discussion

Aikeno's avatar
Aikeno
Helper I
1 year ago
Solved

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 ...
Need Help
  • Visharavana's avatar
    Visharavana
    1 year ago

    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

Anonymous's avatar
Anonymous
Not applicable
1 year ago

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