Summary table based on activity logs

Hi Anonymous,

You can try to add a new calculated table to get your expected result.

Table 2 =
ADDCOLUMNS (
    DISTINCT ( 'RefNum'[RefNum] ),
    "Group Expired",
    VAR t =
        FILTER (
            'RefNum',
            'RefNum'[RefNum] = EARLIER ( [RefNum] )
                && TRIM ( 'RefNum'[Transferred From] ) <> ""
                && TRIM ( 'RefNum'[Transferred To] ) <> ""
        )
    RETURN
        IF (
            COUNTROWS ( t ) = 0,
            MAXX (
                FILTER ( 'Table1', 'Table1'[RefNum] = EARLIER ( [RefNum] ) ),
                [AssignedGroup]
            ),
            MAXX (
                FILTER (
                    t,
                    'RefNum'[Transferred To]
                        = MAXX (
                            FILTER ( 'Table1', 'Table1'[RefNum] = EARLIER ( [RefNum] ) ),
                            [AssignedGroup]
                        )
                ),
                [Transferred From]
            )
        )
)

Best Regards,
Jack Chen