Find Concurrent Usage

[Anonymous]

Hi,

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

From my table named: wrl_tbl_log_1

luid	begin_time	end_time
2099	3/5/2020 4:03:02 PM	3/5/2020 4:06:45 PM
2099	3/5/2020 4:25:32 PM	3/5/2020 4:27:25 PM
2100	3/5/2020 1:11:57 PM	3/5/2020 8:42:07 PM
2099	3/5/2020 3:29:30 PM	null
2100	3/5/2020 3:35:38 PM	null
2099	3/5/2020 6:38:09 PM	3/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

[stretcharm · Accepted solution]

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.

[Anonymous]

Thanks! This solves the small problem. You taught me how CROSSJOIN works. Now I can expand the problem and learn how to perform my larger task. 

[stretcharm]

Glad it works.

Curbal has some more details/videos on the different dax joins

https://curbal.com/blog/glossary/crossjoin-dax

[Greg_Deckler]

Check out Open Tickets. It was designed for this. https://community.powerbi.com/t5/Quick-Measures-Gallery/Open-Tickets/m-p/409364#M147