cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
SanNie84 Frequent Visitor
Frequent Visitor

Bar chart that visualizes number of things during 30 minutes timeframe

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

2 ACCEPTED SOLUTIONS

Accepted Solutions
Community Support Team
Community Support Team

Re: Bar chart that visualizes number of things during 30 minutes timeframe

Hi @SanNie84 ,

 

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 )
        )
    )

11.PNG

 

 

 

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.

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.

View solution in original post

Highlighted
Community Support Team
Community Support Team

Re: Bar chart that visualizes number of things during 30 minutes timeframe

Hi @SanNie84 ,

 

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.

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.

View solution in original post

2 REPLIES 2
Community Support Team
Community Support Team

Re: Bar chart that visualizes number of things during 30 minutes timeframe

Hi @SanNie84 ,

 

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 )
        )
    )

11.PNG

 

 

 

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.

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.

View solution in original post

Highlighted
Community Support Team
Community Support Team

Re: Bar chart that visualizes number of things during 30 minutes timeframe

Hi @SanNie84 ,

 

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.

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.

View solution in original post

Helpful resources

Announcements
Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Ask Amanda Anything Q&A

Ask Amanda Anything Q&A

Learn the answers to some of the questions asked during the Amanda Triple A event.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Users Online
Currently online: 359 members 4,028 guests
Please welcome our newest community members: