cancel
Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

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
PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors