Are your SQL queries that filter with a non-SQL-table slow in PowerBI and PowerQuery in Excel? Then this might be of interest for you:
Nearly 2 years ago when I published my first blogpost about the bug that prevents query folding on SQL-sources when filtered by non-SQL-sources, PowerBI was still so new that I even didn’t mention it in my blogpost. I had enormous problems with some clients work with performance and spent 2-digit hours on figuring out the reason with some good help from the TechNet-Forum.
Today I was notified that “this bug was deemed as not high-pri enough to warrant a fix”. So it’s well alive and sucking: In Excel as well as in PowerBI. Considering how many people read my article, I found it worth to mention again and provide an improved solution. It is a function that’s easy to implement and takes the name of the tables and its key-columns as parameters:
Code to fix Query-Folding bug in PowerBi and PowerQuery
Code to fix Query Folding Bug in PowerBI and PowerQuery
And the code to copy:
Hope this prevents you from wasting precious time.
A break-even analysis tells you at which value of the parameter in question your profit-calculation will turn positive (link). Here we need to sell at least 173 at a given price of 20 before we’ve recovered all our costs:
If your variable costs are constant, you can solve it by this formula:
BreakEvenQuantity = Total Fixed Costs / (Unit Sale Price - Unit Variable Costs)
You’ll find tons of examples on how to do this in Excel like here .
Non-linear cost structure
But in real life, the variable costs often depend on certain quantities as you get discounts for purchasing large amounts. The following table shows a cost structure with fix costs in row 1. The 3rd column “FixOrQty” indicates if the cost item is fix or dependent on the quantity (Qty). The 2nd row contains a variable cost that is constant with 2 for all quantities. Row 3&4 show a variable cost of 8 for quantities up to 100 and if you purchase more than 100 the costs will be lowered to 2 for all additional quantities. Row 5-7 have a similar structure, but with 3 quantity ranges:
Solve with goal-seek algorithm