Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
J1 from 8:00 to 8:05 ,
J2 from 8:06 to 8:10
J3 from 8:11 to 8:15.
By the method explained above for 8-9, it will give count =3 but I want the count as 1
i.e. excluding mutually exclusive calls because they will be sharing the license
Could you please help me with this?
Hi DP86,
I think I was not able to explain my requirement correctly. I will do so now with an example
Id | StartTime | EndTime | |
93718 | 10/15/2021 4:00 | 10/15/2021 4:44 | L1 |
93719 | 10/15/2021 4:03 | 10/15/2021 4:06 | L2 |
93720 | 10/15/2021 4:45 | 10/15/2021 4:48 | L1 |
93721 | 10/15/2021 4:45 | 10/15/2021 4:49 | L2 |
93722 | 10/15/2021 4:54 | 10/15/2021 4:56 | L1 |
93723 | 10/15/2021 4:58 | 10/15/2021 4:58 | L2 |
So in above table we can see 6 jobs that ran between 4-5 but they consumed only 2 licenses.
Now the problem is jobs table does not hold this information that job J1 is using license L1 and so on.
So we have to reach this count 2 based on the start times and end times only.
Requesting your help
@Anonymous, I think it's me who is not explaining clearly...
I will show MY hypothetical example here:
Id | StartTime | EndTime | overlap jobs | L count |
A | 10/15/2021 5:00 | 10/15/2021 5:44 | B, C | 3 |
B | 10/15/2021 5:03 | 10/15/2021 5:06 | A | 2 |
C | 10/15/2021 5:43 | 10/15/2021 5:48 | A, D | 3 |
D | 10/15/2021 5:45 | 10/15/2021 5:49 | C, E | 3 |
E | 10/15/2021 5:49 | 10/15/2021 5:56 | D | 2 |
F | 10/15/2021 5:58 | 10/15/2021 5:58 | - | 1 |
“L count” is number of overlapping jobs + 1.
The maximum number of L count is 3, which would be your max license usage count for the hour.
You can get this table with the following M query:
let
Source = JobsTable,
#"Added All" = Table.AddColumn(Source, "All", each Source),
#"Expanded All" = Table.ExpandTableColumn(#"Added All", "All", {"Id", "StartTime", "EndTime"}, {"Id1", "StartTime1", "EndTime1"}),
#"Changed Type" = Table.TransformColumnTypes(#"Expanded All",{{"StartTime1", type datetime}, {"EndTime1", type datetime}}),
#"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([StartTime1] <= [EndTime]) and [EndTime1]>=[StartTime]),
#"Grouped Rows" = Table.Group(#"Filtered Rows", {"Id", "StartTime", "EndTime"}, {{"L Count", each Table.RowCount(_), Int64.Type}})
in
#"Grouped Rows"
I would use a DAX measure to calculate the max of the "L count" column for the filter context of your choice.
Am I still off-base?
Dear @DP86
Max license count for data shared should be 2 and not 3. Please find below explanation
Id | StartTime | EndTime | ||
A | 10/15/2021 5:00 | 10/15/2021 5:44 | L1 | |
B | 10/15/2021 5:03 | 10/15/2021 5:06 | L2 | |
C | 10/15/2021 5:43 | 10/15/2021 5:48 | L2 | |
D | 10/15/2021 5:45 | 10/15/2021 5:49 | L1 | |
E | 10/15/2021 5:49 | 10/15/2021 5:56 | L2 | |
F | 10/15/2021 5:58 | 10/15/2021 5:58 | L1 |
Where L1 is for first license and L2 is for second license. This license column is only for explanation and we dont have any such column in db
DAX would be great!
In the code below, I did the maximum in M Code, but could easily do it in DAX, depending on what you will be doing with the result.
Using your data, the value returned = 2
let
Source = Excel.CurrentWorkbook(){[Name="Table4"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{
{"Id", type text}, {"StartTime", type datetime}, {"EndTime", type datetime}}),
//Add column = List of all minutes per ID
#"Added Custom" = Table.AddColumn(#"Changed Type", "allTimesw",
each List.DateTimes([StartTime], Duration.TotalMinutes([EndTime]-[StartTime])+1,#duration(0,0,1,0))),
//Remove the other columns
#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Id","StartTime", "EndTime"}),
//Expand the list to rows
#"Expanded allTimes" = Table.ExpandListColumn(#"Removed Columns", "allTimesw"),
//Group the times and count the number at each minute
#"Grouped Rows" = Table.Group(#"Expanded allTimes", {"allTimesw"}, {
{"Num Licences", each Table.RowCount(_)}
}),
//Max license count = max number of rows
#"Calculated Maximum" = List.Max(#"Grouped Rows"[Num Licences])
in
#"Calculated Maximum"
Would I be correct if I paraphrased your requirement as "the maximum concurrency of jobs within a specified period"?
If so, you should be able to do this by:
Overlapping jobs (of Job A) are any jobs whose
Does this help?
Could you provide a DAX for it. I will try.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.