Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.