Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
rmcgrath
Helper I
Helper I

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
ExcelMonke
Continued Contributor
Continued Contributor

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.

View solution in original post

4 REPLIES 4
ExcelMonke
Continued Contributor
Continued Contributor

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

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?

ExcelMonke
Continued Contributor
Continued Contributor

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.

Ah ok - understood!  Thank you for the help!

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.