Reply
rmcgrath
Advocate II
Advocate II
Partially syndicated - Outbound

Visual has exceeded the available resources

Hello.  I am creating a Balance Sheet.  My steps so far have been:

1.  Create and bring in a template

2.  Create a measure to calculate each line item (for the Current Month)

3.  Use the SWITCH function to fill in each line item (in a table)

4.  Repeat for Prior Month

It is at this step I get the resource error.  I'm guessing it's because I have way too many measures in one visual?  Is there a more efficient way to accomplish what I am trying to do?  I was thinking about trying to make Current Month and Prior Month their own 1-column tables, but that is really clunky and does not work unless I include the Template line items as a column.

 

Hopefully the screenshots help show what I'm trying to do.

balance sheet.jpgcurrent month.jpgprior month.jpg

 

1 ACCEPTED SOLUTION

Syndicated - Outbound

That's correct, it creates a different table, where, a lot of these calculations would be done on the respective rows. From my understanding, you would like to have your SELECTEDVALUE clause act as a filter of sorts. However it doesn't quite act that way in an efficient manner, if that makes sense? With this table, you cna just filter to the right row, rather than do SELECTEDVALUE Calculations. 

This is just my hunch, you may have to do some testing to see if this gets you your result as intended. It is hard to do testing for this on my end in terms of pushing the limits of PBI to recreate your issue. 
Of note, you do have quite a lot of clauses in your switch value ... depending on your end goal, this may not be the most efficient way of tackling this problem. 

I am basing my initial response by taking your initial posting at face value without understanding the underlying structure of the data 🙂 You may have better luck searching for posts where folks have created balance sheets.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!





View solution in original post

4 REPLIES 4
ExcelMonke
Super User
Super User

Syndicated - Outbound

There may be more elegant solutions out there, but what you could try is creating a new table with the function:

 

 

ADDCOLUMNS(
VALUES('Balance Sheet Template'[Line Item ID]),
"Measure", [Your Switch Measure Here]
)

 

 

Where your Switch Measure is:

 

 

SWITCH(
Balance Sheet Template[Line Item ID],
"Cash", ... ,
etc,
)

 

 

Then, rather having Power BI calculate based on the selectedvalue, you can just filter to the line item ID you need.

If you have access to DAX Studio, I recommend doing some performance testing in there. This video (watch on 1.5x speed if you don't want to spend 1 hour of your life🤠) is extremely helpful in squeezing performance out of your PBIX: https://www.youtube.com/watch?v=sCQQ-ZYBDAA





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!





Syndicated - Outbound

Thank you for replying!

 

I'm not quite sure I fully understand?  So this would be a completely different table?  I was hoping to only have 1 table which shows Current Month and Prior Month?

Syndicated - Outbound

That's correct, it creates a different table, where, a lot of these calculations would be done on the respective rows. From my understanding, you would like to have your SELECTEDVALUE clause act as a filter of sorts. However it doesn't quite act that way in an efficient manner, if that makes sense? With this table, you cna just filter to the right row, rather than do SELECTEDVALUE Calculations. 

This is just my hunch, you may have to do some testing to see if this gets you your result as intended. It is hard to do testing for this on my end in terms of pushing the limits of PBI to recreate your issue. 
Of note, you do have quite a lot of clauses in your switch value ... depending on your end goal, this may not be the most efficient way of tackling this problem. 

I am basing my initial response by taking your initial posting at face value without understanding the underlying structure of the data 🙂 You may have better luck searching for posts where folks have created balance sheets.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!





Syndicated - Outbound

Ah ok - understood!  Thank you for the help!

avatar user

Helpful resources

Announcements
March PBI video - carousel

Power BI Monthly Update - March 2025

Check out the March 2025 Power BI update to learn about new features.

March2025 Carousel

Fabric Community Update - March 2025

Find out what's new and trending in the Fabric community.

Top Solution Authors (Last Month)
Top Kudoed Authors (Last Month)