topic Re: Getting maximum concurrent license usage only in Desktop

Getting maximum concurrent license usage only

Anonymous — Tue, 11 Jan 2022 19:07:11 GMT

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!

Re: Getting maximum concurrent license usage only

Greg_Deckler — Tue, 11 Jan 2022 19:29:15 GMT

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.

Re: Getting maximum concurrent license usage only

Anonymous — Wed, 12 Jan 2022 08:41:54 GMT

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!

Re: Getting maximum concurrent license usage only

Greg_Deckler — Wed, 12 Jan 2022 13:33:36 GMT

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())

Re: Getting maximum concurrent license usage only

Anonymous — Wed, 12 Jan 2022 13:46:14 GMT

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.

Re: Getting maximum concurrent license usage only

Greg_Deckler — Wed, 12 Jan 2022 16:03:50 GMT

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

Re: Getting maximum concurrent license usage only

Anonymous — Wed, 12 Jan 2022 17:40:48 GMT

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

Re: Getting maximum concurrent license usage only

v-chenwuz-msft — Fri, 14 Jan 2022 09:11:28 GMT

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.

Re: Getting maximum concurrent license usage only

Anonymous — Thu, 20 Jan 2022 13:18:18 GMT

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?

Re: Getting maximum concurrent license usage only

v-chenwuz-msft — Fri, 21 Jan 2022 02:12:19 GMT

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.

Re: Getting maximum concurrent license usage only

Anonymous — Fri, 21 Jan 2022 08:09:43 GMT

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

Re: Getting maximum concurrent license usage only

Anonymous — Fri, 21 Jan 2022 18:33:43 GMT

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

Attaching pbix file