Forum Discussion
Find Concurrent Usage
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.
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.
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.
Glad it works.
Curbal has some more details/videos on the different dax joins
https://curbal.com/blog/glossary/crossjoin-dax