Forum Discussion

Idonotknowthis's avatar
Idonotknowthis
New Member
2 years ago
Solved

IF Function with multiple conditions

Hello, everyone! New here, so any help is appreciated...

I have:  

Table 1

CaseID
a556kk
b67rgg
v0102g
f384kk
b68rga
c456gk

 

Table 2

CaseIDRelatedBug-IDTypeOfBug
a556kk0023technical
b67rgg0014reqtype
b68rga0018nontech
c456gk0045other

 

I want to create a new column in Table 1 that will return "Yes" only if the CaseID from Table1 is found in Table2, and is responsive to "nontech" and "reqtype" only. Otherwise (CaseID is not found in Table2 or is found but the TypeOfBug is "technical" or "other") to return "No": 

Table 1 

CaseIDAssociatedReq?
a556kkNo
b67rggYes
v0102gNo
f384kkNo
b68rgaYes
c456gkNo

How can I do that? I assume it's with IF function. Thanks in advance to everyone who contributed!

Need Help
  • Daniel29195's avatar
    Daniel29195
    2 years ago

    Idonotknowthis 

    output 

     

    calculated column : 

    Column = 
var ds = 
FILTER(tbl_2,
tbl_2[CaseID] = tbl_1[CaseID] && (tbl_2[TypeOfBug] = "nontech" || tbl_2[TypeOfBug] = "reqtype")) 

RETURN
if( ISEMPTY(ds), "NO", "YES")

     

     

    If my answer helped sort things out for you, i would appreciate a thumbs up 👍 and mark it as the solution ✅ to help other people to find it more quickly . 

     

  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi Idonotknowthis ,
    Daniel29195 Great solution, here's mine.

    AssociatedReq = 
IF(
    CALCULATE(
        COUNTROWS(Table2),
        FILTER(
            Table2,
            Table2[CaseID] = EARLIER(Table1[CaseID]) &&
            (Table2[TypeOfBug] = "nontech" || Table2[TypeOfBug] = "reqtype")
        )
    ) > 0,
    "Yes",
    "No"
)

    Final output

     

    Best regards,

    Albert He

     

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

4 Replies

  • Daniel29195's avatar
    Daniel29195
    Community Champion
    2 years ago

    Idonotknowthis 

    output 

     

    calculated column : 

    Column = 
var ds = 
FILTER(tbl_2,
tbl_2[CaseID] = tbl_1[CaseID] && (tbl_2[TypeOfBug] = "nontech" || tbl_2[TypeOfBug] = "reqtype")) 

RETURN
if( ISEMPTY(ds), "NO", "YES")

     

     

    If my answer helped sort things out for you, i would appreciate a thumbs up 👍 and mark it as the solution ✅ to help other people to find it more quickly . 

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi Idonotknowthis ,
    Daniel29195 Great solution, here's mine.

    AssociatedReq = 
IF(
    CALCULATE(
        COUNTROWS(Table2),
        FILTER(
            Table2,
            Table2[CaseID] = EARLIER(Table1[CaseID]) &&
            (Table2[TypeOfBug] = "nontech" || Table2[TypeOfBug] = "reqtype")
        )
    ) > 0,
    "Yes",
    "No"
)

    Final output

     

    Best regards,

    Albert He

     

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

    • Idonotknowthis's avatar
      Idonotknowthis
      New Member
      2 years ago

      Thanks for sharing another way of dealing with such tasks! Works perfectly