IF Function with multiple conditions

[Idonotknowthis]

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

I have:  

Table 1

CaseID

a556kk

b67rgg

v0102g

f384kk

b68rga

c456gk

Table 2

CaseID	RelatedBug-ID	TypeOfBug
a556kk	0023	technical
b67rgg	0014	reqtype
b68rga	0018	nontech
c456gk	0045	other

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 

CaseID	AssociatedReq?
a556kk	No
b67rgg	Yes
v0102g	No
f384kk	No
b68rga	Yes
c456gk	No

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

[Daniel29195 · Accepted solution]

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

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]

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

[Daniel29195 · Accepted solution]

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

[Idonotknowthis]

Thank you! It works perfectly!