Forum Discussion

MikeKKK's avatar
MikeKKK
Frequent Visitor
4 years ago

Classic workspace Usage Metric - Trend View calculation

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? 

 

Need Help

3 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    4 years ago

    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's avatar
      MikeKKK
      Frequent Visitor
      4 years ago

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

  • v-chenwuz-msft's avatar
    v-chenwuz-msft
    Icon for Community Support rankCommunity Support
    4 years ago

    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.
     
    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.