cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
kavita_asthana
Frequent Visitor

Maximum uage count of licenses for the jobs every hour

 for eg. for 8-9 I count all the jobs that EXCLUDE below conditions:
 
        StartTime > 9  OR Endtime <8
 
This will give me all the jobs that were using the license between 8-9
 
Although, it doesn't fulfill scenario to exclude the repeated counts of mutually exclusive jobs. This scenario is explained below with an example:
 
Example: If there are mutually exclusive jobs like

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?

6 REPLIES 6
kavita_asthana
Frequent Visitor

Hi DP86,

 

I think I was not able to explain my requirement correctly. I will do so now with an example

 

IdStartTimeEndTime 
9371810/15/2021 4:0010/15/2021 4:44L1
9371910/15/2021 4:0310/15/2021 4:06L2
9372010/15/2021 4:4510/15/2021 4:48L1
9372110/15/2021 4:4510/15/2021 4:49L2
9372210/15/2021 4:5410/15/2021 4:56L1
9372310/15/2021 4:5810/15/2021 4:58L2

 

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

@kavita_asthana, 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

IdStartTimeEndTime  
A10/15/2021 5:0010/15/2021 5:44 L1
B10/15/2021 5:0310/15/2021 5:06 L2
C10/15/2021 5:4310/15/2021 5:48 L2
D10/15/2021 5:4510/15/2021 5:49 L1
E10/15/2021 5:4910/15/2021 5:56 L2
F10/15/2021 5:5810/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!

  • Add a custom column which is  an inclusive List at one minute intervals from starttime to endtime
  • Delete the other columns
  • Expand the List column then Group By Rows and return the Count
  • The number of licenses will be equal to the maximum of the count column

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"

 

DP86
Advocate II
Advocate II

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:

  1. couting the overlapping jobs for each job; then
  2. getting the maximum of the counts for a specific period

Overlapping jobs (of Job A) are any jobs whose

  • start times are not after the ending time of A; and
  • end times are not before start time of A.

Does this help?

Could you provide a DAX for it. I will try.

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Top Kudoed Authors