Today I want to share a scenario where a running total calculation in the query editor saved a model that run out of memory when done with DAX:
The model couldn’t be refreshed and returned out of memory error with a calculated column in the fact table of over 20 Mio rows (from a csv-file). A running total should be calculated for each “JourneyID”, of which there were over 1 Mio in the table itself. This rose memory consumption during refresh by over 300 % – until it finally errored out:
SUM ( Fact[Entries] )
– SUM ( Fact[Exits] );
ALLEXCEPT ( Fact; Fact[JourneyID] );
<= EARLIER ( Fact[StopId] )
This is a post about a feature request, that definitely would make my life easier (and I guess others’ too):
Evaluation context and its transition is one of the hardest concepts in DAX and I would find it extremely helpful, if syntax highlighting would assist us here. Let the colour of the table name show whether the expression will be evaluated:
Edit 14-Dec-2017: Now that we can right-align text measures in PowerBI, a SWITCH-measure like here: http://www.thebiccountant.com/2017/04/24/kpis-in-easy-profit-and-loss-for-powerbi/#comment-719 is the best alternative in my eyes. No need to read on 🙂
As shown in my last part of the Easy P&L-series, Power BI unfortunately still lacks some fundamental formatting options like:
- Right aligning text (please vote for it here: Right align text in Power BI – edit 15th Nov: We’re there: Right aligning text is available now: https://powerbi.microsoft.com/en-us/blog/power-bi-desktop-november-2017-feature-summary/)
- Display numbers in different formats within one column (either to be implemented as a “neutral” format for Switch-measures, where the referenced measures carry the formatting attributes already, or as a part of a formula-based conditional formatting) (Thanks Matt for the voting-link: Conditional format SWITCH measure)
So for the moment I choose between the following workaround-options:
- Display %-values in a separate column
- Format numbers as text and fill up with spaces so that all end up right aligned
- See the suggestion from Matt Allington in the comments below (very nice)
Right aligning text or percentage figure in new column
For both options the preparations are the same:
If you use the M-function Number.Mod in Power BI or Power Query and expect the same result like in Excel or DAX, you are probably in good company.
But if the signs of the number and the divisor are not the same, M will differ from Excel and DAX:
Number.Mod in M is different
This is by design, so you can use this this formula instead, if you need matching results:
[Number] – [Divisor] * Number.RoundDown( [Number] / [Divisor] )
Enjoy & stay queryious 🙂
Following up on the BOM-explosion: A comment reminded me that I had missed to present the costing techniques to calculate the total costs of each (sub)-product.
Reversing the aggregation direction
What I had shown is how to “aggregate” from parent down to child-level to retrieve the total quantity of each component within a BOM (“How many of each (sub-) components do we have to order (or build) for that bike?”) (1).
Now we reverse the aggregation direction and aggregate the total (!) quantities back up to the parents (2).
And, as this doesn’t make too much sense in an economical way, the second aggregation will be their prices (3). This will give us the sum of all part-costs (“How much will the order of all the parts cost us?”). This is also very useful for planning purposes or reconciliation of prices for intermediate products with your master data.
And if your model holds sales-data as well, you can calculate the totals costs of your total sales within each period. (4)
VAR 1: Using classical hierarchies
Edit 12-Jan-18: Code and file updated (robustness & speed)
Handling multilevel bill of materials (BOM) without VBA in Excel and PowerBI is now a piece of cake: Just pass 4 parameters into the M-function below and it will return a table that holds everything you need for:
Order list (“total quantities”)
3. Implosion (“where used”): Will be covered in next blogpost
The format of the input-data for this function needs to be like the example used from the Adventure Works 2008-database, where all products on the top of the hierarchy also have an entry in the child-column (the components), leaving the parent column blank: