Optimizing Report

[Otto_Luvpuppy]

Having an absolute nightmare and can't identify the culprit...although it's probably me!

I have a report that is constructed from 'mixed' data...we have a Direct Query input from Azure, some local SharePoint List data and a couple of manual tables (minimal lines). Aproximately 5k rows in all for the whole thing. I have many Measures and some of them reference other Measures (nested). The calculations are fine, they work, but every time I edit a measure or change anything for that matter I wait 20-25 minutes for the report to process. Yes, I know...it's mad. I've run the Analyser and this is what I get:

as you can see no more than a second or two!

What on earth is going on? Where else can I look? Is it too late to join a monestery? 😖

Thanks

Paul

[AlexisOlson]

Not really a solution, but a workaround to waiting every time you edit something is to do your edits in Tabular Editor and sync the edits to your .pbix only when you choose to.

[Otto_Luvpuppy]

Hi Alexis,

Unfortunately we aren't allowed to install Tabular...company won't allow it...don't ask. That said, even if I could do it there is still the question of 'why 25 minutes to process?'. Something is very wrong and I can't work out where to start looking.
Thanks for the prompt reply though.
Paul

[AlexisOlson]

It's likely that your model is complex or has lots of calculated columns despite being small in terms of the number of rows. Without actually seeing it, it's hard to make any specific recommendations.

You may learn some things from these prior related discussions:
https://community.powerbi.com/t5/Desktop/Why-modifying-any-measure-in-PowerBI-Desktop-is-taking-so-l...

https://community.powerbi.com/t5/Desktop/The-PBI-file-gets-extremely-slow-after-creating-a-few-calcu...

[Otto_Luvpuppy]

Thanks Alexis,

Yes it is complex, well, lots of calculated columns and even tables...not complex in themselves but simply the quantity. Unfortunately I can't get around it as the Direct Query data isn't available for me to do that sort of work in Power Query, the tables just aren't visible. So I have to do the transmornation using DAX.....not what I would choose to do but I have no option.
Thanks fot the links though....I'll take a look today.
Regards
Paul

[AlexisOlson]

Note that you are able to do some transformations on DriectQuery data in the query editor. It's just more limited since they have to be transformations that can be folded and passed back to be evaluated at the source. Filtering and grouping should be possible along with some other basic functions that are easily translatable into a native query.