Classic workspace Usage Metric - Trend View calculation

[MikeKKK]

Happy New Year everyone!

I need a help on customised Power BI usage metric report.

I am using live connection to Usage Metrics dataset (classic workspace)for my customized usage report, and I would like to have "View Trend" (it is only available in new workspace usage metric dataset.) 

The view trend reflects view count changes over time. It compares the first half of the selected time period with the second half. 

Thus, I created a measure as below:

View Trend =

VAR StartDate = MIN(Dates[Date])

VAR EndDate = MAX(Dates[Date])

VAR PeriodHalfDaysCount = INT((MAX(Dates[Date])-MIN(Dates[Date]))/2)

VAR MidDate = StartDate+PeriodHalfDaysCount

VAR FirstHalfView = CALCULATE([ViewsCount], DATESBETWEEN(Dates[Date],StartDate,MidDate))

VAR SecondHalfView = CALCULATE([ViewsCount], DATESBETWEEN(Dates[Date],MidDate+1,Enddate))

RETURN DIVIDE (SecondHalfView-FirstHalfView,FirstHalfView)

NOTE: [ViewsCount] is the predefined measure in Usage Metrics dataset.

This measure returns correct value but the calculation is taking extremely long time.   It is in tens of second while other predefined measures takes less than 1 seconds.

Am I doing the right thing here? Is there anything wrong with my measure? 

[v-chenwuz-msft]

Hi @MikeKKK ,

I recommend you to check the startdate and enddate of each report if they are all the same. Because the relationship between 'Date' table and other report table is one to many and the cross filter direction is single.

Then in your code, maybe the following code is better.

VAR FirstHalfView = CALCULATE([ViewsCount], 'Date'[Date]<=MidDate)

VAR SecondHalfView = CALCULATE([ViewsCount], 'Date'[Date]>MidDate)

For more methods of optimisation, please refer to the following article.

https://maqsoftware.com/insights/dax-best-practices 

Or you can use an external tool, DAX Studio, to check the performance of dax.

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.

[amitchandak]

@MikeKKK  , Try with this change

View Trend =
VAR StartDate = MINX(allselected(Dates) ,Dates[Date])
VAR EndDate = MAXX(allselected(Dates) ,Dates[Date])
VAR PeriodHalfDaysCount = INT((MAX(Dates[Date])-MIN(Dates[Date]))/2)
VAR MidDate = StartDate+PeriodHalfDaysCount
VAR FirstHalfView = CALCULATE([ViewsCount], DATESBETWEEN(Dates[Date],StartDate,MidDate))
VAR SecondHalfView = CALCULATE([ViewsCount], DATESBETWEEN(Dates[Date],MidDate+1,Enddate))

RETURN DIVIDE (SecondHalfView-FirstHalfView,FirstHalfView)

[MikeKKK]

@amitchandak   I can see you have changed the Variable StartDate and EndDate , how does this help?