Get value from another table in inactive relationship

[Anonymous]

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

[Anonymous]

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 · Accepted solution]

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.

[Anonymous]

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.