Forum Discussion
Advocate IIIVisual has exceeded the available resources
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.
Impactful IndividualThere 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
rmcgrathThank 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
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!