Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Performance hit after model change

Hi,

 

I updated model of one of my reports recently and noticed that it caused huge performance hit when filtering by clicking on visualizations. It works fine when I click on my table visualization but clicking one of other charts makes table reload for like five minutes. I used DAX Studio to see if there is any partiular query that makes it so bad and there is - partialy. The query runs for 70-80 seconds on average but full reload still takes up to five minutes. You can find the old query here and the new one here.

 

I do realize those queries might not be enough to solve the issue. Can you provide me with any guidence what more information should I post to let you help me? Maybe my measures and other two DAX queries autogenerated would be helpful too?

 

EDIT:

I worked a little with DAX Studio and managed to identify most problematic measures. Can anyone help me fix them, write them better?

 

 

planned = 
    SUMX(
         KEEPFILTERS(VALUES(WorkStatuses[certain_operation]));
         CALCULATE(SUM(WorkStatuses[duration])/Count(WorkStatuses[duration]))
    )
actual = 
    SUMX(
         KEEPFILTERS(VALUES(WorkStatuses[certain_operation]));
         CALCULATE(SUM(WorkStatuses[Hours]))
    )

 

 

The idea behind those measure is there are rows, each has ProjectId, TaskId, EmployeeId, PlannedTime, WorkedTime. When two employees work on the same task we have to sum that time but don't sum time planned. So example data are:

ProjectIdTaskIdEmployeeIdPlannedTimeWorkedTime
120012h1h
120022h0.5h
120032h0.25h

 

The expected output of planned measure is to be 2h, however actual should return 1.75h. Is it possible to make it work efficient?

  • Anonymous's avatar
    Anonymous
    6 years ago

    The problem was actually the model, or maybe rather DAX generated while cross filtering. Power BI created one filter variable that contained filtering rules comming from two dimension tables around the fact table. Breaking it into two in DAX Studio made things work within eye blink but as I had no influence on code Power BI generates I simply changed the model, so one of the measure tables got joined into fact table and now it works fine. My measurements also appear to be good as report performance is now good enough for my users.

3 Replies

  • v-yuta-msft's avatar
    v-yuta-msft
    Community Support

    Anonymous ,

     

    Create a measure using dax below:

    Result =
    CALCULATE ( SUM ( Table[WorkedTime] ), ALLEXCEPT ( Table, Table[TaskId] ) )
    

    Community Support Team _ Jimmy Tao

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

     

    • Anonymous's avatar
      Anonymous
      Not applicable

      Hi,

       

      thank you for your response. Your formula seems to speed things up significantly, as the run time of whole query dropped donw to like 45 seconds. What about the other one? Do you have any idea? Unfortunately I still didn't figured it out. The main problem I have there is no need of calculating much as this value is already assigned in every column, so I would be theoretically possible to just put it in the table but then when I summarize the table column in my visualization and two employees worked on the same task the value gets doubled and it is wrong but I still need column sum on the bottom. Can you help with that?

      • Anonymous's avatar
        Anonymous
        Not applicable

        The problem was actually the model, or maybe rather DAX generated while cross filtering. Power BI created one filter variable that contained filtering rules comming from two dimension tables around the fact table. Breaking it into two in DAX Studio made things work within eye blink but as I had no influence on code Power BI generates I simply changed the model, so one of the measure tables got joined into fact table and now it works fine. My measurements also appear to be good as report performance is now good enough for my users.