Optimizing formulas for faster query load times

Many of my in report queries are taking too long to update. Additionally, after talking to Microsoft Technical Support they state this is the reason why my refreshes are increasingly taking longer and my scheduled refreshes time out and fail on the Power BI Service.

Their advice was that I should optimize my formulas and reduce the load times. I do not have the technical skills to optimize beyond what I created this far. Although my report is modeled the way my organization needs it and I have created the measures needed I’m not certain what I could revise to improve speed.

I got all the way to the finish line but I can’t cross it because apparently I have written inefficient measures.

Can someone help look at my model or advise of a consulting resource that can help improve my model and measure efficiency?

@Swhs24 Sure, we can try to optimize the code. Please share your PBI file, for best solutions.

1 Like

the size of my PBIX is too large for your current limits. Is there another sharing mechanism I can give you access on? I can share directly from onedrive or gdrive if that’s possible.

Please advise.


@Swhs24 You can share over google drive/one drive.

The file can be access HERE: https://drive.google.com/drive/folders/1lP1a2qU6fIwCqG6knqkMAwHFPaLCar0q?usp=sharing

Thanks for posting your question @Swhs24 To receive a resolution in a timely manner please make sure that you provide all the necessary details on this thread.

Here is a potential list of additional information to include in this thread; demo pbix file, images of the entire scenario you are dealing with, screenshot of the data model, details of how you want to visualize a result, and any other supporting links and details.

Including all of the above will likely enable a quick solution to your question.

@Swhs24 Wow that’s a lot of bi-directional filters, and that’s not good. You will have to de-normalize this database

Regarding optimization, I would suggest that you start by removing the columns that you don’t need i.e. the ones with Blue lines in the image below.

Primary key in a fact table is a crime in PBI/SSAS/Power Pivot, you can get arrested by Tabular Police:

Replace string keys with integer ones, order ID in G-Price Detail is gibberish, strings take up more space to store than integers

I removed Id column from F-Order and G-Price tables and size already dropped to 368MB from 480MB

And turn off the auto date time feature and use a proper date table, there are a lot of implicit hidden date tables created in your file, remember PBI is different from Tableau.

Remember the most optimized column is the one that doesn’t exist in your table.


Great Value added here @AntrikshSharma and some excellent suggestions.


Firstly, thank you, there’s a lot of feedback here which I will employ to optimize.

The first point I will focus on is this comment:

I thought I had a “proper date table.” This date table was a solution from ENDA. I created it from EDNA code, gave it a custom name [!Dates (Core Table)] but followed the instruction on how to - what am I missing? What’s wrong with this table? Is there a problem with the join or something else?

Oh okay, I only focused on the Core Table part, you are good with that, just go to options and uncheck the auto date time option, when you have this option activated engine creates a new date table for each table which has a date field.

Ok, thank you. I did uncheck that.

Is my date joins ok?

I was hoping to connect all tables to one date table but it would create some relationship conflicts so I only connected the ones that are not a conflict right now. I plan to use the treatas function later but I’ve never used it before some I cautious

Hi @Swhs24, a response on this post has been tagged as “Solution”. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the check box. Also, we’ve recently launched the Enterprise DNA Forum User Experience Survey, please feel free to answer it and give your insights on how we can further improve the Support forum. Thanks!