Summarise Tables and Group

[oldmanraskers]

Hello,

Can anyone shed light on how I would take a datetime list of faults from a table and a datetime list of ink_changes from another table and make a second table that gives the faults / ink_changes on a line by line basis grouped by hour from the datetimes, for example:

Table 1 Faults

Datetime               Fault Variant

02/12/17 08:00     Fault Type 1

02/12/17 08:22     Fault Type 2

02/12/17 08:42     Fault Type 2

02/12/17 08:44     Fault Type 2

02/12/17 08:49     Fault Type 2

02/12/17 09:17     Fault Type 1

02/12/17 09:23     Fault Type 2

02/12/17 09:51     Fault Type 2

02/12/17 09:59     Fault Type 3

Table 2 Ink Changes

Datetime

02/12/17 08:00

02/12/17 08:49

02/12/17 09:30

02/12/17 09:23

02/12/17 11:14

Table 3 Faults Per Change

Hour     Fault Variant     Rate

08         Fault Type 1      1

08         Fault Type 2      2

09         Fault Type 3      NULL

09         Fault Type 1      0.5

09         Fault Type 2      1

09         Fault Type 3      0.5

10         Fault Type 1      NULL

10         Fault Type 2      NULL

10         Fault Type 3      NULL

etc.

Thanks,

Raskers

[v-shex-msft · Accepted solution]

Hi @oldmanraskers,

You can refer to below steps to create 'Faults Per Change' table.

Steps:

1. Add calculate column 'last change date' to faults table.

Last Change Date =
CALCULATE (
    MAX ( 'Link changes'[Datetime] ),
    FILTER ( ALL ( 'Link changes' ), [Datetime] <= EARLIER ( Faults[Datetime] ) )
)

2. Write a calculate table to get the summary 'per change fault' table.

Faults Per Change =
VAR original =
    // summary faults table by last change date.
    SUMMARIZE (
        Faults,
        [Last Change Date],
        Faults[Fault Variant],
        "Rate", COUNT ( Faults[Datetime] )
    )
VAR missing_Fault =
    /* use crossjoin to generate detail change date fault list, use 
except function to compare with orignail table to get missing fault records.*/
    ADDCOLUMNS (
        EXCEPT (
            CROSSJOIN (
                VALUES ( 'Link changes'[Datetime] ),
                VALUES ( Faults[Fault Variant] )
            ),
            SELECTCOLUMNS (
                original,
                "Datetime", [Last Change Date],
                "Fault Variant", [Fault Variant]
            )
        ),
        "Rate", 0
    )
VAR merged =
    // merge original table and missing list to get full list, add date and hour as summary column
    ADDCOLUMNS (
        UNION ( original, missing_Fault ),
        "Date", DATEVALUE ( [Last Change Date] ),
        "Hour", HOUR ( [Last Change Date] )
    )
RETURN
    SUMMARIZE (
        merged,
        [Date],
        [Hour],
        [Fault Variant],
        "Rate", SUMX (
            FILTER (
                merged,
                [Date] = EARLIER ( [Date] )
                    && [Hour] = EARLIER ( [Hour] )
                    && [Fault Variant] = EARLIER ( [Fault Variant] )
            ),
            [Rate]
        )
    )

Result:

Regards,

Xiaoxin Sheng