Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Find Concurrent Usage

Hi,

 

I am trying to find the peak/concurrent usage of an application during the day.

From my table named: wrl_tbl_log_1

luidbegin_timeend_time
20993/5/2020 4:03:02 PM3/5/2020 4:06:45 PM
20993/5/2020 4:25:32 PM3/5/2020 4:27:25 PM
21003/5/2020 1:11:57 PM3/5/2020 8:42:07 PM
20993/5/2020 3:29:30 PMnull
21003/5/2020 3:35:38 PMnull
20993/5/2020 6:38:09 PM3/5/2020 7:04:04 PM

 

I am stumped trying to determine how the dates all overlap over eachother.

My end goal is to find the overlap during a day and make a graph like the following.

 

 

If someone could point me in the correct direction, I would really appreciate it.

 

-M

Need Help
  • stretcharm's avatar
    stretcharm
    6 years ago

     

    You could create a list of all the hours with an active Usage record.

     

    You can either have a table of hours or generate them in M

     

    let
    Source = List.Generate(()=>0, each _ < 24, each _ + 1),
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Column1", Int64.Type}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Column1", "Hour"}})    
in
    #"Renamed Columns"

     

     

    Now we can cross join the Usage data and filter just the hours that are between the start and end.

    Create a New Dax Table (under Modeling)

     

    HourUsage = FILTER(CROSSJOIN(Hours,'Usage'), AND(Hours[Hour]>=Hour(Usage[begin_time]), Hours[Hour]<=HOUR(Usage[end_time]) ))

     

     

    You can then chart the hours and count distinct uids.

     

4 Replies

  • stretcharm's avatar
    stretcharm
    Memorable Member
    6 years ago

     

    You could create a list of all the hours with an active Usage record.

     

    You can either have a table of hours or generate them in M

     

    let
    Source = List.Generate(()=>0, each _ < 24, each _ + 1),
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Column1", Int64.Type}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Column1", "Hour"}})    
in
    #"Renamed Columns"

     

     

    Now we can cross join the Usage data and filter just the hours that are between the start and end.

    Create a New Dax Table (under Modeling)

     

    HourUsage = FILTER(CROSSJOIN(Hours,'Usage'), AND(Hours[Hour]>=Hour(Usage[begin_time]), Hours[Hour]<=HOUR(Usage[end_time]) ))

     

     

    You can then chart the hours and count distinct uids.