"Query has exceeded the available resources" error only occurs during 'initial opening of report'

[PBIFanHook7]

The "query has exceeded the available resources" error 'only occurs during the initial opening' of the power bi desktop report which uses direct query. After the error occurs, I can click refresh or close/reopen the report and it loads in 8-9 seconds each and every time after the failure during the initial opening of the report for that particular day. 

This report has a small data model with two DIM views in a one to many relationship with the ETL Fact View (4200 rows, 14 columns). One DIM View has 134 rows with 10 columns and the other DIM View has 13 rows with 10 columns. The Single cross-filter direction is used. The 'Make this relationship active' option is selected. The 'Assume referential integrity' option is not selected. The main visual is a table and it takes about 5 seconds to load. There is no complicated DAX, no calculated columns and only 1 measure which is: Total Records =  COUNTROWS (ETL Fact View).

There is an independant View which returns one record with the Load Date which is displayed in a Card visual on the first report tab and displayed in a Card visual on the second report tab which is just the Help Page. I just noticed (as I am gathering data for this post) when I select the Card visual on the first report tab, in the Fields area this Load Date shows as 'Latest Load Date'. But, on the  second report tab (Help Page) the Load Date shows as 'Earliest Load Date'. I am not sure why this is defaulting to Latest on one tab and Earliest on the other tab.  

I am still researching, and read the below comments from the below link. 

https://blog.crossjoin.co.uk/2023/06/18/the-visual-has-exceeded-the-available-resources-error-in-pow...

Two comments:
I ran into the Resources exceeded just by switching a column in a table from “Don’t Summarize” to “Summarize”.
I change the Invoice Date column from “Don’t summarize” to “Latest,” it bombs out and tells me the visual exceeds resources.

I am still researching. Another issue is that since the report only times out on the initial opening of the report for that day (it appears), I can only change something and try it once a day. Is there another way to do test if a change worked or not with this scenario (only occurs on initial opening of the report once for that day)?

Thanks in advance for any suggestions or help. 

The Performance Analyzer Results are below: 

Some of the report settings: 

[v-karpurapud]

Hi @PBIFanHook7 

We have not received a response from you regarding the query and were following up to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions.

Thank You.

[v-karpurapud]

Hi @PBIFanHook7 

I wanted to check if you’ve had a chance to review the information provided. If you have any further questions, please let us know. Has your issue been resolved? If not, please share more details so we can assist you further.

Thank You.

[v-karpurapud]

Hi @PBIFanHook7

Thank you for your question on the Microsoft Fabric Community Forum.

This error happens because Power BI Desktop processes DirectQuery differently during the initial metadata load. The Load Date field is automatically summarized, with one card set to “Earliest” and another to “Latest,” leading to inconsistent processing. This causes extra load before caching is in place, resulting in the “query has exceeded the available resources” error. To fix this, create explicit DAX measures like Latest Load Date = MAX('LoadDateView'[LoadDate]) or Earliest Load Date = MIN('LoadDateView'[LoadDate]) and use them in your card visuals. Additionally, consider disabling background data previews, setting parallel loading to one, and turning off Auto Date/Time for new columns.

I hope this information is helpful. . If you have any further questions, please let us know. we can assist you further.

Regards,

Microsoft Fabric Community Support Team.

[PBIFanHook7]

I thought I had this issue resolved but it has come back. I am going to have to try what you suggested regarding creating explicit DAX measures like Latest Load Date = MAX('LoadDateView'[LoadDate]) or Earliest Load Date = MIN('LoadDateView'[LoadDate]) and use them in my card visuals. Thanks. 

[v-karpurapud]

Hi @PBIFanHook7 

Thank you for the update.  After testing, kindly share your your observations with us. If you have any further questions or require additional support, please feel free to reach out.

Regards,

Microsoft Fabric Community Support Team.

[PBIFanHook7 · Accepted solution]

Thanks for the excellent feedback. I will try to implement some of what you suggested. In the mean time, I believe I have resolved this issue by doing the below (My solution below). I have one question regarding the Auto Date/Time for new columns option, does this negatively affect performance because it generates date/time table for every date column? (When the option is enabled, Power BI Desktop creates a hidden auto date/time table for each date column, for Import Mode Tables with date or date/time column data types and isn't on the "many" side of a model relationship.)

My solution, I went back and looked at some data types and removed/re-added some joins. I removed summarization from columns that were not required to be summarized. I checked join columns data types. The Date DIM View Date Column was being used to join to the ETL FACT View Date Column and both of these date columns had a data type of date/time. I changed these date columns to a date data type. The Location DIM View was joining to the ETL FACT View on Location Key. The Location Key on both sides of the join had a data type of decimal number, I changed it to a whole number. I removed the above mentioned joins and re-created them. The report has been opening okay on the initial opening of the report and appears to be running a bit faster.

[v-karpurapud]

Hi @PBIFanHook7 
Thank You for the Update.

Does the Auto Date/Time for new columns option negatively affect performance because it generates a hidden date/time table for every date column?

Yes, enabling Auto Date/Time can negatively affect performance because it generates hidden date tables for every eligible date column, increasing overhead and model complexity. Since your model already has a Date Dimension table, it’s best practice to disable Auto Date/Time and rely on your existing Date DIM for all date-related logic. This will improve efficiency and keep the model cleaner.

Regards,
Microsoft Fabric Community Support Team.

[kushanNa]

Hi @PBIFanHook7 

Have you tried clearing the cache and publishing it into a test workspace to see if the issue persists? That way we can make sure there's no issue with the existing published model.

[PBIFanHook7]

I tried clearing the cache. This error is happening in power bi desktop. This report is not published to a workspace. Thanks. 

[PBIFanHook7]

Thanks for the feedback. Yes, I tried clearing the cache. This is only a pbi desktop report for now. I will post more information. I think I have resolved the issue.