cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
hirokichi
New Member

Measurement performance for calculating the number of concurrent events

Hi there,

The dataset consists of columns for ID, start time, and end time, with tens of thousands of rows.

To observe the number of simultaneous events in minutes, I created the following query.
However, the performance is poor.
Please let me know if there is a better way.

 

#DataSet

Download

 

#Query

SampleTime

let
Source = Csv.Document(File.Contents("C:\SampleTime.csv"),[Delimiter=",", Columns=3, Encoding=65001, QuoteStyle=QuoteStyle.None]),
Header = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
ConvertType = Table.TransformColumnTypes(Header,{{"Id", Int64.Type}, {"StartDate", type datetime}, {"EndDate", type datetime}}),
Custom = Table.AddColumn(ConvertType, "Round_StartDate", each DateTime.From(Number.RoundDown(Number.From(DateTime.From([StartDate]))*(24*60))/(24*60))),
Custom1 = Table.AddColumn(Custom, "Round_EndDate", each DateTime.From(Number.RoundUp(Number.From(DateTime.From([EndDate]))*(24*60))/(24*60)))
in
Custom1

Concurrent

let
    StartDateTime = DateTime.From(Date.From(List.Min(SampleTime[StartDate]))),
    EndDateTime = Date.AddDays(DateTime.From(Date.From(List.Max(SampleTime[EndDate]))), 1),
    Source = List.DateTimes(StartDateTime, Duration.TotalMinutes(EndDateTime - StartDateTime), #duration(0, 0, 1, 0)),
    ConvertToTable = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    RenameColumn = Table.RenameColumns(ConvertToTable,{{"Column1", "DateTime"}}),
    ConvertType = Table.TransformColumnTypes(RenameColumn,{{"DateTime", type datetime}}),
    TableBuffer = Table.Buffer(SampleTime),
    Custom = Table.AddColumn(ConvertType, "Concurrent", each Table.RowCount(Table.SelectRows(TableBuffer, (x) => x[Round_StartDate] <= [DateTime] and x[Round_EndDate] >= [DateTime])))
in
    Custom

 

1 ACCEPTED SOLUTION
AlexisOlson
Super User
Super User

Your Custom step is iterating through the entire TableBuffer and filtering for each of the 100k+ rows of ConvertType. Not terribly efficient.

 

I propose the following redefinition of Concurrent:

 

let
    Source = SampleTime,
    #"Added Custom" =
        Table.AddColumn(Source, "Custom", each
            List.DateTimes(
                [Round_StartDate],
                Duration.TotalMinutes([Round_EndDate] - [Round_StartDate]),
                #duration(0, 0, 1, 0))
        ),
    #"Removed Columns" = Table.SelectColumns(#"Added Custom",{"Id", "Custom"}),
    #"Expanded Custom" = Table.ExpandListColumn(#"Removed Columns", "Custom"),
    #"Grouped Rows" = Table.Group(#"Expanded Custom", {"Custom"}, {{"IdCount", each Table.RowCount(Table.Distinct(_)), Int64.Type}})
in
    #"Grouped Rows"

 

This evaluates much faster.

View solution in original post

2 REPLIES 2
AlexisOlson
Super User
Super User

Your Custom step is iterating through the entire TableBuffer and filtering for each of the 100k+ rows of ConvertType. Not terribly efficient.

 

I propose the following redefinition of Concurrent:

 

let
    Source = SampleTime,
    #"Added Custom" =
        Table.AddColumn(Source, "Custom", each
            List.DateTimes(
                [Round_StartDate],
                Duration.TotalMinutes([Round_EndDate] - [Round_StartDate]),
                #duration(0, 0, 1, 0))
        ),
    #"Removed Columns" = Table.SelectColumns(#"Added Custom",{"Id", "Custom"}),
    #"Expanded Custom" = Table.ExpandListColumn(#"Removed Columns", "Custom"),
    #"Grouped Rows" = Table.Group(#"Expanded Custom", {"Custom"}, {{"IdCount", each Table.RowCount(Table.Distinct(_)), Int64.Type}})
in
    #"Grouped Rows"

 

This evaluates much faster.

View solution in original post

Thank you for your advice!
Queries are much faster!

Helpful resources

Announcements
Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

User Group Leader Meeting January 768x460.png

Calling all User Group Leaders!

Don't miss the User Group Leader meetings on January, 24th & 25th, 2022.

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors