Custom Column with multiple if statements

[JamesMoody]

I am wanting to add a column to my data to determine when a call is abandoned. Here is my currrent formula and the result I am getting.

Formula:  Column = IF('Table1'[Participant] = "agent", IF('Table1'[Segment] <> "interact", "Abandoned"))

Current Result:

Record ID	Participant	Segment	Current Result
1	agent	alert	Abandoned
1	ivr	ivr
1	acd	interact
1	acd	delay
1	customer	interact
2	agent	wrapup	Abandoned
2	agent	interact
2	agent	alert	Abandoned
2	acd	interact
2	acd	delay
2	ivr	ivr
2	customer	interact

I would like the result to be as per below - only counting records as Abandoned if one of the participants is an 'agent' and there is only the one segment for 'alert'

Expected Result:

Record ID	Participant	Segment	Expected Result
1	agent	alert	Abandoned
1	ivr	ivr
1	acd	interact
1	acd	delay
1	customer	interact
2	agent	wrapup
2	agent	interact
2	agent	alert
2	acd	interact
2	acd	delay
2	ivr	ivr
2	customer	interact

[Jihwan_Kim · Accepted solution]

Hi,

Please check the below picture and the attached pbix file.

Expected Result CC =
VAR _recordid = Table1[Record ID]
VAR _list =
    CALCULATETABLE (
        Table1,
        Table1[Record ID] = _recordid,
        Table1[Participant] = "agent",
        Table1[Segment] <> "alert"
    )
RETURN
    IF ( COUNTROWS ( _list ) = 0 && Table1[Segment] = "alert", "Abandoned" )

[JamesMoody]

Thanks, this solved my issue!