Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
ItoDiaz
Helper I
Helper I

IF statements with conditions in another table

Hello, 

 

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

Annul_sessions.JPG

 

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

 

GuytableGuytable

 

TrainingtableTrainingtable

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? 

 

 

1 ACCEPTED SOLUTION
v-zhangti
Community Support
Community Support

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")

vzhangti_0-1672282006881.png

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.

View solution in original post

2 REPLIES 2
v-zhangti
Community Support
Community Support

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")

vzhangti_0-1672282006881.png

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.

@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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.