Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Summary table based on activity logs

Hi everyone, I'm hoping someone can assist with a query. I have two tables with lots of data - 1 million+ lines in each one. Table 1 Each line in this table shows a ticket ID (RefNum) and t...
Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    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

Anonymous's avatar
Anonymous
Not applicable
6 years ago

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