cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
mmerchak
Advocate II
Advocate II

Visual resources exceeded error when only minor changes to measure and page filters are made

I will start out by saying that this issue only shows up in Service (shared capacity, not premium) but I am posting it in Desktop, because  I believe that is where the issue lies. Even though the visual shows up fine in desktop, after loading for just a few seconds.

 

I have a table that is getting a "resources exceeded" error once uploaded to the Service on the Expenses Breakdown page (shared capacity not premium, so there is no way for me to increase the per-query memory limit as far as I am aware).

 

The thing that has me scratching my head is that an almost identical page (Revenue Breakdown) is rendering just fine. The measure and page filter have just a minor tweak from the one that is working to the one that is not working (marked in orange boxes in the pictures that have the measures).

 

My data set is rather large, it has data going back to 2014. If I reduce the data to go back only to 2020 I do not have this issue. I would like to be able to keep all of the data unless a solution is not found. I just do not understand why the Revenue Breakdown page and measure work, and the Expenses Breakdown page and measure don't work. Well, I will also mention that the Expense Breakdown measure works in the table, but not the matrix.

 

Measures:

 

 

 

 

Gross Revenue Breakdown - Year = IF(SELECTEDVALUE('Year Budget Values'[Year]) = "Budget - Latest Year"
, CALCULATE([Budget],AcctDesc[Financial Acct Type] = "Rev",FILTER('Budget Table', 'Budget Table'[Year] = MAX('Month Year Values'[Year])))
, CALCULATE([Actuals],AcctDesc[Financial Acct Type] = "Rev",FILTER('GL Table', YEAR('GL Table'[GLDate]) <= SELECTEDVALUE('Month Year Values'[Year]) && YEAR('GL Table'[GLDate]) >= SELECTEDVALUE('Month Year Values'[Year]) - ([Rolling Years Value] - 1)), USERELATIONSHIP('Year Budget Values'[Year],'Date'[Year - Text])))
Net Expense OpExp Breakdown - Year = IF(SELECTEDVALUE('Year Budget Values'[Year]) = "Budget - Latest Year"
, CALCULATE([Budget],AcctDesc[Financial Acct Type] = "Equipment & Supplies",FILTER('Budget Table','Budget Table'[Year] = MAX('Month Year Values'[Year])))
, CALCULATE([Actuals],AcctDesc[Financial Acct Type] = "Equipment & Supplies",FILTER('GL Table', YEAR('GL Table'[GLDate]) <= SELECTEDVALUE('Month Year Values'[Year]) && YEAR('GL Table'[GLDate]) >= SELECTEDVALUE('Month Year Values'[Year]) - ([Rolling Years Value] - 1)), USERELATIONSHIP('Year Budget Values'[Year],'Date'[Year - Text])))
Budget = CALCULATE(-SUM('Budget Table'[TRANSACTIONCURRENCYAMOUNT]), 'Budget Table'[Exclude 64000] = 1)
Actuals = CALCULATE(-SUM('GL Table'[TRANSACTIONCURRENCYAMOUNT]), 'GL Table'[Exclude 64000] = 1)

 

 

 

 

 

Expenses Breakdown page error in service:

Expenses Page Error in Service.png

 

Relationships in report:

Relationships.png

 

Revenue Breakdown page and measure (colored boxes correspond to the columns/values that go together - orange boxes are what are changed in the Expenses Breakdown page and measure):

Revenue Page Measure.png

 

Expenses Breakdown page and measure (colored boxes correspond to the columns/values that go together - orange boxes are what were changed from the Revenue Breakdown page and measure):

Expenses Page Messure.png

1 ACCEPTED SOLUTION
mmerchak
Advocate II
Advocate II

Just for anyone reading this now, I was able to fix the issue by creating a summary table in Power Query using only the columns I needed. The issue seemed to be the number of Expense records compared to the number of Revenue records. This worked like a charm!

View solution in original post

2 REPLIES 2
mmerchak
Advocate II
Advocate II

Just for anyone reading this now, I was able to fix the issue by creating a summary table in Power Query using only the columns I needed. The issue seemed to be the number of Expense records compared to the number of Revenue records. This worked like a charm!

lbendlin
Super User
Super User

Use DAX Studio to analyze the queries produced for the offending visual.  Look for excessive number of records in the query plan.  Think about ways to reformulate your DAX queries (if appropriate) or data model (if not)

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

Thank you 2022 Review

2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.