Bar chart that visualizes number of things during 30 minutes timeframe

[Anonymous]

Hi there,

I have a question. I have a data set that shows a certain number of events during the day (at what time does someone leave the building). Each event has also a time notation (hh:mm:ss). Is it possible to make a bar chart where each bar shows the number of events during timeframes of 30 minutes? (e.g. from  08h00-08h30 ; 08h30-09h00). Or does anyone know another way to model this data?

Hope to hear soon from you! 

Cheers, 

Sander

[v-lid-msft · Accepted solution]

Hi @Anonymous ,

Firstly, we can use following M Query to create a table as x-axis:

let
    Source = List.Times(#time(0,0,0),48,#duration(0,0,30,0)),
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Added Custom" = Table.AddColumn(#"Converted to Table", "Custom", each [Column1] + #duration(0,0,30,0)),
    #"Changed Type" = Table.TransformColumnTypes(#"Added Custom",{{"Column1", type text}, {"Custom", type text}}),
    #"Inserted Merged Column" = Table.AddColumn(#"Changed Type", "Merged", each Text.Combine({[Column1], [Custom]}, " - "), type text),
    #"Removed Columns" = Table.RemoveColumns(#"Inserted Merged Column",{"Column1", "Custom"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Merged", "Duartion"}}),
    #"Added Index" = Table.AddIndexColumn(#"Renamed Columns", "Index", 0, 30)
in
    #"Added Index"

Then we can create a measure to count the event:

Count = 
VAR t =
    ADDCOLUMNS (
        ALL ( Query1 ),
        "m", HOUR ( 'Query1'[Time] ) * 60
            + MINUTE ( 'Query1'[Time] )
    )
RETURN
    COUNTROWS (
        FILTER (
            t,
            AND ( [m] >= MIN ( Query2[Index] ), [m] < MIN ( Query2[Index] ) + 30 )
        )
    )

BTW, pbix as attached.

Best regards,

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-lid-msft · Accepted solution]

Hi @Anonymous ,

How about the result after you follow the suggestions mentioned in my original post?

Best regards,

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-lid-msft · Accepted solution]

Hi @Anonymous ,

Firstly, we can use following M Query to create a table as x-axis:

let
    Source = List.Times(#time(0,0,0),48,#duration(0,0,30,0)),
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Added Custom" = Table.AddColumn(#"Converted to Table", "Custom", each [Column1] + #duration(0,0,30,0)),
    #"Changed Type" = Table.TransformColumnTypes(#"Added Custom",{{"Column1", type text}, {"Custom", type text}}),
    #"Inserted Merged Column" = Table.AddColumn(#"Changed Type", "Merged", each Text.Combine({[Column1], [Custom]}, " - "), type text),
    #"Removed Columns" = Table.RemoveColumns(#"Inserted Merged Column",{"Column1", "Custom"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Merged", "Duartion"}}),
    #"Added Index" = Table.AddIndexColumn(#"Renamed Columns", "Index", 0, 30)
in
    #"Added Index"

Then we can create a measure to count the event:

Count = 
VAR t =
    ADDCOLUMNS (
        ALL ( Query1 ),
        "m", HOUR ( 'Query1'[Time] ) * 60
            + MINUTE ( 'Query1'[Time] )
    )
RETURN
    COUNTROWS (
        FILTER (
            t,
            AND ( [m] >= MIN ( Query2[Index] ), [m] < MIN ( Query2[Index] ) + 30 )
        )
    )

BTW, pbix as attached.

Best regards,

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.