Visual has exceeded the available resources

[rmcgrath]

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.

[ExcelMonke]

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

[rmcgrath]

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 · Accepted solution]

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.

[rmcgrath]

Ah ok - understood!  Thank you for the help!