Getting maximum concurrent license usage only

[Anonymous]

Dear Team,

I have to find out the maximum concurrent license usage in a day. So, if user is selecting a date say 6th Jan 2022, it should show maximum concurrent licenses used that day. 

I am able to find out maximum license usage for each second of selected date i.e. for 86400 rows. But, what i want is only those seconds where concurrent license consumption was maximum.

The mesaure I created for concurrent license usage is 

LicenseUsageSecJob = CALCULATE(COUNT(Jobs[Id]), FILTER(Jobs,
(
(SELECTEDVALUE(DateTimeSec[DateTime]) >= Jobs[StartTime] && SELECTEDVALUE(DateTimeSec[DateTime]) <= Jobs[EndTime])||(SELECTEDVALUE(DateTimeSec[DateTime]) >= Jobs[StartTime] && Jobs[EndTime] = BLANK())
)
)
)

In a table visual I have 2 fields- DateTimeSec[DateTime] and LicenseUsageSecJob

Now this table visual has 86400 rows, if the maximum concurrent licenses used on 6th Jan were 12, I want only those time periods where LicenseUsageSecJob=12.

How to acheive this? Please help me! Its urgent!

[Greg_Deckler]

@Anonymous Use MAXX, so something like:

Maximum Concurrent = 
  MAXX(
    ADDCOLUMNS(
      DISTINCT('DateTimeSec'[DateTime],
      "Concurrent",[LicenseUsageSecJob]
    ),
    [Concurrent]
  )

You could then use the Filter pane or another selector measure to only show the rows where the value is the max.

[Anonymous]

My report looks like this(screenshot above). When the user selects a date the table on right displays the maximum concurrent calls for all the seconds for that day i.e. 86400 rowswhich the table visual is not able to display.

Now the key ask here is that how to show only those records in table visual which has maximum concurrent calls, it could be 30-40 rows for eg. and not 86400 rows for each second.

@Greg_Deckler The measure shared by you when placed with datetime field in a table visual still gives all the seconds rows. Requesting your help!

[Greg_Deckler]

@Anonymous  I think you missed this in the previous post "You could then use the Filter pane or another selector measure to only show the rows where the value is the max." So, try this:

Maximum Concurrent Flag =
VAR __Max 
  MAXX(
    ADDCOLUMNS(
      DISTINCT(ALL('DateTimeSec'[DateTime]),
      "Concurrent",[LicenseUsageSecJob]
    ),
    [Concurrent]
  )
VAR __Count = CALCULATE(COUNT(Jobs[Id]), FILTER(Jobs,(
(SELECTEDVALUE(DateTimeSec[DateTime]) >= Jobs[StartTime] && SELECTEDVALUE(DateTimeSec[DateTime]) <= Jobs[EndTime])||(SELECTEDVALUE(DateTimeSec[DateTime]) >= Jobs[StartTime] && Jobs[EndTime] = BLANK()))))
RETURN
IF(__Count = __Max, __Count,BLANK())

[Anonymous]

Filter pane does not accept Max function. Also, Var _Max calculates for all datetime and not selected value, hence it will do calculation for 30 days*86400=2592000 rows which is not a feasible option.

[Greg_Deckler]

@Anonymous You can add a measure into the Filters on this visual section of the Filters pane.

[Anonymous]

@Greg_Deckler : Yes, I can add a measure in filter pane but what will be the value?

[v-chenwuz-msft]

Hi @Anonymous ,

You can start by creating an aggregate table to find the maximum value at each time, as @Greg_Deckler  says _Max, and store it in the cache as a calculation table to improve performance.

Then create a measure, such as

measuredd if =
IF(
    [LicenseUsageSecJob]
        = CALCULATE(
            MAX( 'new table'[countjobs] ),
            'new table'[DateTime]
                = SELECTEDVALUE( DateTimeSec[DateTime] )
        ),
    1,
    0
)

put the measure in filter pane and set show item is 1.

You can share your pbix file without sensitive data if you need more help.

Best Regards

Community Support Team _ chenwu zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi @v-chenwuz-msft ,

Thankyou for the reply but I did not understood this part " store it in the cache as a calculation table to improve performance." Is there any technote or any example of how to do this?

[v-chenwuz-msft]

Hi @Anonymous ,

That means create a new table in power bi data. In power bi, you can use DAX to create columns or measures.

Columns

Power BI calculates and saves the results when the user has finished writing the dax immediately.

Measures

Power BI calculates result depend on the current content when the user uses this measure.

As you said, "it will do calculation for 30 days*86400=2592000 rows which is not a feasible option.", so, creating a new table or column in advance, rather than adding this table as a procedure table in the measure, will improve performance. 

Then create a measure i have posted for filter pane.

You can share your pbix file without sensitive data if you need more help.

Best Regards

Community Support Team _ chenwu zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi @v-chenwuz-msft ,

Is it possible to calculate maximum licenses used only for the dates selected in new table. For eg:

If user selects 14th Jan 2022, then all the time periods of that date should have maximum license value in new table

Is this possible? If yes, how?

Also, I am modifying my pbix file to keep only required data and will attach it shortly

[Anonymous]

https://inoffice-my.sharepoint.com/personal/kavita_asthana_emea_adecco_net/Documents/Attachments/Lic... 

Attaching pbix file