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.

Helpful resources

Announcements
May 23 2022 epsiode 5 without aka link.jpg

The Power BI Community Show

Welcome to the Power BI Community Show! Jeroen ter Heerdt talks about the importance of Data Modeling.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

Power BI Dev Camp Session 22 with aka link 768x460.jpg

Check it out!

Mark your calendars and join us on Thursday, May 26 at 11a PDT for a great session with Ted Pattison!

Power BI Release May 2022 768x460.png

Check it out!

Click here to read more about the May 2022 updates!