Forum Discussion

oldmanraskers's avatar
oldmanraskers
Frequent Visitor
8 years ago
Solved

Summarise Tables and Group

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

Need Help
  • Anonymous's avatar
    Anonymous
    8 years ago

    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

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    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