cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
trevb
Resolver I
Resolver I

Dreadful performance

I'm having dreadful problems with Performance on my Surface Pro 3 i7.  Queries were slow so I offloaded a lot of work into DAX but that now means everybodies experience is slow as I'm not doing so much in the back end.

 

To give some idea I have one table that I download and trim out as many columns as possible as early as possible.  Against that data I run the query below to create a Pivot

 

//Query pivots the data so that we can spread costs across financial years it then sets nulls to zero and renames the column for humans to understand more easily
let
Source = Projects,
JustProjectCode = Table.SelectColumns(Source,{"ProjectCode","ProjectStartDate","ProjectFinishDate"}),
AddStartFY =
Table.ExpandListColumn(
Table.AddColumn(JustProjectCode ,
"SFY",
each List.Generate(
()=>GetEarliestFY(Projects[ProjectStartDate]),
each _<= GetLatestFY(Projects[ProjectFinishDate]),
each Date.AddYears(_,1)
)

),
"SFY"
),
AddEndFY = Table.AddColumn(AddStartFY, "EFY", each Date.AddDays(Date.AddYears([SFY],1),-1)),
AddFYTitle = Table.AddColumn(AddEndFY , "Financial Year", each "Work " & Number.ToText(Date.Year([SFY])) & "/" & Number.ToText(Number.Mod(Date.Year([EFY]),1000))),
AddCoreStart = Table.AddColumn(AddFYTitle, "Start", each List.Max({[SFY],[ProjectStartDate]})),
AddCoreEnd = Table.AddColumn(AddCoreStart, "End", each List.Min({[EFY],[ProjectFinishDate]})),
AddDaysEffort = Table.AddColumn(AddCoreEnd, "DaysEffort", each if [Start] < [End] then Duration.Days([End]-[Start])+1 else 0, type number),
#"Removed Columns" = Table.RemoveColumns(AddDaysEffort,{"ProjectStartDate", "ProjectFinishDate", "SFY", "EFY", "Start", "End"}),
#"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[#"Financial Year"]), "Financial Year", "DaysEffort", List.Sum)
in
#"Pivoted Column"

 

I set this off at 11:40 this morning, got bored by 12:00, had lunch and went for a walk and returned 13:00 to find the query still running!  This can't be right but I cannot work out if this is the machine or the query.  Does anyone have any ideas?

 

1 ACCEPTED SOLUTION
trevb
Resolver I
Resolver I

And now I answer my own question.

 

Nestled in the below is this "GetEarliestFY(Projects[ProjectStartDate])".  This is calling a powerbi function that works out the earliest financial year from the provided column.  Apart from the fact that this being an function potentially has an overhead I think it is being called for every itteration of the loop along with it's compatriot.

 

by changing this to #dates on both of those the query finished in a little over 15 seconds.  🙂  Some work to be done on this I think.

View solution in original post

1 REPLY 1
trevb
Resolver I
Resolver I

And now I answer my own question.

 

Nestled in the below is this "GetEarliestFY(Projects[ProjectStartDate])".  This is calling a powerbi function that works out the earliest financial year from the provided column.  Apart from the fact that this being an function potentially has an overhead I think it is being called for every itteration of the loop along with it's compatriot.

 

by changing this to #dates on both of those the query finished in a little over 15 seconds.  🙂  Some work to be done on this I think.

View solution in original post

Helpful resources

Announcements
Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

User Group Leader Meeting January 768x460.png

Calling all User Group Leaders!

Don't miss the User Group Leader meetings on January, 24th & 25th, 2022.

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors