Forum Discussion

equlibrum96's avatar
equlibrum96
New Member
2 years ago
Solved

Filtering by iterator in calculated column

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_idfirst_contact_date
first_call_date_after first_contact
107-10-20239-10-2023
208-10-202310-10-2023

 

case_calls table

case_idcall_id
1a
1b
1c
2d
2e

 

calls table

 

call_idcall_datestatus
a6-10-2023picked up
b8-10-2023missed
c9-10-2023picked up
d5-10-2023picked up
e10-10-2023picked 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's avatar
    v-zhangti
    2 years ago

    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.

2 Replies

  • tamerj1's avatar
    tamerj1
    Community Champion
    2 years ago

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

  • v-zhangti's avatar
    v-zhangti
    Community Support
    2 years ago

    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.