Performance hit after model change

[Anonymous]

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:

ProjectId	TaskId	EmployeeId	PlannedTime	WorkedTime
1	2	001	2h	1h
1	2	002	2h	0.5h
1	2	003	2h	0.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?

[v-yuta-msft]

@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]

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 · Accepted solution]

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.