Filtering by iterator in calculated column

[equlibrum96]

Hello everyone

Hello, I am a young PowerBi adept and I need help in calculating a new calculated column 

My data looks like this: I have a case table, a calls table, and the so-called bridge tables combining case and calls (case_calls)

case table

case_id	first_contact_date	first_call_date_after first_contact
1	07-10-2023	9-10-2023
2	08-10-2023	10-10-2023

case_calls table

case_id	call_id
1	a
1	b
1	c
2	d
2	e

calls table

call_id	call_date	status
a	6-10-2023	picked up
b	8-10-2023	missed
c	9-10-2023	picked up
d	5-10-2023	picked up
e	10-10-2023	picked up

My need is to calculate the first_call_date_after first_contact column in case table (marked in red)

The definition of this column is the date of the first related call for which:
1) call_date>= first_contact_date AND status= "picked up"

In other words, for each case_id in the case table, I want to calculate the date of the first picked up calls (status = "picked up") after the date of the first contact (first_contact_date)

I would be very grateful for your help 🙂 

[v-zhangti · Accepted solution]

Hi, @equlibrum96 

You can try the following methods.

Measure = 
CALCULATE ( MIN ( 'calls table'[call_date] ),
    FILTER ( ALL ( 'calls table' ),
        [status] = "picked up"
            && [call_date] > MAX ( 'case table'[first_contact_date] )
            && RELATED ( 'case_calls table'[call_id] ) = MAX ( 'calls table'[call_id] )
    )
)

Column = CALCULATE ( MIN ( 'calls table'[call_date] ),
    FILTER ( 'calls table' ,
        [status] = "picked up"
            && [call_date] > EARLIER( 'case table'[first_contact_date] )
            && RELATED ( 'case_calls table'[case_id] ) = EARLIER('case table'[case_id])
    )
)

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.

[tamerj1]

Hi @equlibrum96 

Please try

first_call_date_after first_contact =
MINX (
FILTER (
CALCULATETABLE ( Calls, TREATAS ( { Case[Case_Id] }, Case_Calls[Case_Id] ) ),
Calls[Call_Date] > Case[First_Contact_Date]
&& Calls[Status] = "Picked Up"
),
Calls[Call_Date]
)