Forum Discussion

vvirul's avatar
vvirul
New Member
3 years ago

Performance improvement

I am trying to modify this query so that is loads in shortest amount of time, currently my query takes more that 20 seconds to load:

Below is the DAX query

 

Response % Complete Overtime =

var max_date = MAX('Date Table'[Date])
var temp = FILTER(
                   ALLSELECTED('Date Table'[Date]),
                   ISONORAFTER('Date Table'[Date], max_date, DESC)
)

var running_total = CALCULATE(
                                distinctcount(Responses_overtime[id]),
                                Responses_overtime[status] <> BLANK(),
                                USERELATIONSHIP('Date Table'[Date],Responses_overtime[updated_date]),
                                temp)
 
var number_needed = CALCULATE(
                                     DISTINCTCOUNTNOBLANK('Responses_overtime'[id]),
                                     USERELATIONSHIP('Date Table'[Date],Responses_overtime[updated_date]),
                                     temp)

var result = IF(SELECTEDVALUE('Date Table'[Date],blank())<=TODAY(),running_total/number_needed ,blank())
return result
 
 
Please help, Thanks

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi vvirul ,

     

    Please try this.

     

    Response % Complete Overtime =

var max_date = LASTDATE('Date Table'[Date])
var temp = FILTER('Date Table', 'Date Table'[Date] <= max_date)

var running_total = CALCULATE(
 countrows(Responses_overtime),
 Responses_overtime[status] <> BLANK(),
 USERELATIONSHIP('Date Table'[Date],Responses_overtime[updated_date]),
  temp)
 
var number_needed = CALCULATE(
 countrows(Responses_overtime),
 USERELATIONSHIP('Date Table'[Date],Responses_overtime[updated_date]),
 temp)

var result = DIVIDE(running_total, number_needed, BLANK())
return result

    You can also optimize the performance by optimizing the model, etc.

    Refer : Optimization guide for Power BI - Power BI | Microsoft Learn

    Power BI Performance Optimization Tips (mssqltips.com)

     

     

    Best Regards,

    Neeko Tang

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

    • vvirul's avatar
      vvirul
      New Member
      3 years ago

      Thanks for the solution but this did not give the expected result. It definately tool less than a second to give result. I will use this solution somewhere else 🙂

  • vvirul's avatar
    vvirul
    New Member
    3 years ago

    I solved the problem and below is the solution,

    Response % Complete Overtime--DATE =

    var max_date = MAX('Date Table'[Date])

    var temp = FILTER(

            ALLSELECTED('Date Table'[Date]),

            ISONORAFTER('Date Table'[Date], max_date, DESC)

        )

    var running_total = CALCULATE(

         SUMX(VALUES(Responses_overtime[id]),1),

        Responses_overtime[status] <> BLANK(),

        USERELATIONSHIP('Date Table'[Date],Responses_overtime[updated_date]),

        temp

    )

    var number_needed = CALCULATE(

         SUMX(VALUES(Responses_overtime[id]),1),

        USERELATIONSHIP('Date Table'[Date],Responses_overtime[updated_date]),

        temp

    )

    var result = IF(SELECTEDVALUE('Date Table'[Date],blank())<=TODAY(),running_total/number_needed ,blank())

    return result



    The performance is much more improved and taking less than 6-7 seconds.