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:
So for the moment I choose between the following workaround-options:
For both options the preparations are the same:
Today I’ll show you a very useful technique how to deal with a JSON record that contains a wild mixture of different elements like this:
If you click on one of the expandable elements, their content will be shown, but you’ll loose all the “surrounding” information (metadata) that is visible now. This is often an issue, regardless if you want to create multiple tables from it to build a star-schema or just need a handful of fields or a denormalized table. But with a little help from M, you’re good to go:
Table.FromRecords( { MyJsonRecord } )Will returns this:
With this move, every expansion of one of the expandable elements will keep the existing data in place:
Simply expand one element after each other to create a denormalized table
For multiple tables, keep this query and reference it to create you (sub-)tables. Always keep the Id-column as the key (!) to combine all the tables in your data model later. (Provided you use this in a function for multiple entities/series)
Best is to play with it, so just past this code into the advanced editor:
If your JSON-record has a different structure with “just” header and data in different fields, this technique will be more suitable for you: http://www.thebiccountant.com/2016/04/23/universal-json-opener-for-quandl/
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.
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)
It’s downing a bit slowly on me how awesome the direct retrieval of DMVs in PowerBI and PowerQuery actually is. Just see how easy we can build a cool view of our memory consumption in PowerBI:
Left: Tables – Right: Columns
RAM/Memory Consumption of whole data model
While the relationships view of the datamodel provides a very good overview which tables are connected to each other, one cannot see at a glance on which field they are connected to each other.
This is where a pivot table-view of the field-connections can be really helpful:
Table-Fields-Connections in Pivot-View
Here you see the tables on the many-side in the rows and in the columns are the tables on the one-side (of course you can change that). Add some slicers if your model is very large.