Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Get value from another table in inactive relationship

Hello, i have two tables with

 

Table 1 : 

Key Status

A Verified

B Verified

C Closed

 

Table 2 

Key 1 Key 2

A        B

A        C

 

And i want a result table like this : 

 

Key 1 Key 1_Statut Key 2 Key2_Status

A      Verified     B     Verified

A      Verified     C      Closed 

 

 

The first relationship is between Table1[Key] and Table 2[Key 1] and is 1-to many 

 

I make a second relationship that is inactive between Table1[Key] and Table2 [Key 2] to get status from values that are Key 2, 1 to many too 

 

But i don't success 

 

I tried

 

CALCULATE(MAX(Table1[Status]),USERELATIONSHIP(Table1[Key],Table2[Key 2]) but it don't work, with SELECTEDVALUE it don't works too ... Do you have an idea to do it ? 

 

Thank you in advance

Need Help
Show and Tell
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi Anonymous 

    You can refer to the following measure

    Sample table 2

     

    Measure = IF(SELECTEDVALUE('Table 2'[Key1])=BLANK(),"Null",CALCULATE(MAX('Table 1'[Status]),'Table 1'[Key]=SELECTEDVALUE('Table 2'[Key1])))
    Measure 2 = IF(SELECTEDVALUE('Table 2'[Key2])=BLANK(),"Null",CALCULATE(MAX('Table 1'[Status]),'Table 1'[Key]=SELECTEDVALUE('Table 2'[Key2])))

    Output

    Best Regards!

    Yolo Zhu

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi Anonymous 

    It's better that you delete the relationship between two tables, then create two meaure

    Measure = CALCULATE(MAX('Table 1'[Status]),'Table 1'[Key]=SELECTEDVALUE('Table 2'[Key1]))
    Measure 2 = CALCULATE(MAX('Table 1'[Status]),'Table 1'[Key]=SELECTEDVALUE('Table 2'[Key2]))

    Output

    Best Regards!

    Yolo Zhu

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hello thank you it works for this exemple, but sometime my table 2 looks like 

     

     

    Table 2

    Key 1 Key 2

    B 

     

     

    There is no Key 2, and when i use your formula, the line with B disappears because there is no matching between Key 2 and Table 1 key, but i want to keep the values where Key 2 is no here ...

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      Hi Anonymous 

      You can refer to the following measure

      Sample table 2

       

      Measure = IF(SELECTEDVALUE('Table 2'[Key1])=BLANK(),"Null",CALCULATE(MAX('Table 1'[Status]),'Table 1'[Key]=SELECTEDVALUE('Table 2'[Key1])))
      Measure 2 = IF(SELECTEDVALUE('Table 2'[Key2])=BLANK(),"Null",CALCULATE(MAX('Table 1'[Status]),'Table 1'[Key]=SELECTEDVALUE('Table 2'[Key2])))

      Output

      Best Regards!

      Yolo Zhu

      If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.