IF statements with conditions in another table

[ItoDiaz]

Hello, 

I have two tables linked by a common column "Id T. "

I have a table with guys who should follow a traning and a second table with events regarding some tranings. 

Guytable

Trainingtable

I need to add a column to my 'guystable' to show the final reason of cancelation of the training like this :

IF Guytable(ID T)=Trainingtable (ID T) then

IF Guytable(Cancel reason) = "SAP" then "SAP"
IF Guytable(Cancel reason) = "SRP" || IF Guytable(Cancel reason) = "" && Trainingtable(Cancel reason) = "" then "SRP"
IF Guytable(Cancel reason) = "OAT" && Trainingtable(Cancel reason) = "EO" then "SUP"
IF Guytable(Cancel reason) = "OAT" && Trainingtable(Cancel reason) = "OAT" then "OAT"
IF Guytable(Cancel reason) = "OAT" && Trainingtable(Cancel reason) = "" then "OAT"
else
"AMP"

I have tried using Lookupvalue and related fonctions but DAX sent a message saying that de colunms doesn't exist or doesn't have a relationship with a table....

Someone could give some ideas to get my final cancelation reason column please? 

[v-zhangti · Accepted solution]

Hi, @ItoDiaz 

You can try the following methods.
Column:

Trainingtable reason = LOOKUPVALUE(Trainingtable[Cancel reason],Trainingtable[Id T],[Id T])

Column = 
SWITCH(TRUE(),
[Cancel reason]="SAP","SAP",
OR([Cancel reason]="SRP",[Cancel reason]=""&&[Trainingtable reason]=""),"SRP",
[Cancel reason]="OAT"&&[Trainingtable reason]="EO","SUP",
[Cancel reason]="OAT"&&[Trainingtable reason]="OAT","OAT",
[Cancel reason]="OAT"&&[Trainingtable reason]="","OAT",
"AMP")

Is this the result you expect?

Best Regards,

Community Support Team _Charlotte

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

[ItoDiaz]

@v-zhangti , 

Thanks a lot for your quick answer and sorry for my late feedback. 

It works perfectly and I got a better understanding of how the Lookupvalue and Switch work.