Alberto Ferrari has recently published a very smart concept how to analyze events with a duration in DAX, which you should read here, if you haven’t done yet. It simplifies the necessary DAX-syntax and speeds up the calculations as well. My following approach simplifies the DAX-syntax even more, but it comes with a (very tiny) premium for performance and will also increase the file size a bit. So you have the choice 🙂
I’m transforming the calculated table into a “real” fact-table which enables me to use simple 1:n-relations to the other (now) dimension-tables:
The formula starts from Alberto’s first version, but uses the Date instead of the DateKey (yellow). Then there will be some columns added which we need for following calculations (green). Then you see that the DailyProductionValue is calculated at a different place and also has a much simpler syntax. At last there are some other columns for further calculations: “Shipped” and “Ordered” will create the bridge for the “missing” connections to the date-table:
Marco Russo has created a great tool for SSAS tabular that lets you edit measure definitions (which you should read here first if you haven’t done yet).
In this article I’ll show you how you can use it to import multiple measures from different tabular models into your current model.
The way the DAX-editor works is that it exports the existing measures from your model as a text file and imports them back after you’ve done your transformations. My technique will add the measures from the other model automatically to the existing measures so that both can be loaded back into your current model. In addition to that, you will have a UI with a process that guides you through the necessary steps that come with a task like that, which are:
select only those measures that you actually need
check references to existing measures and columns from the import model and manage their handling
allocate the tables into which these measures are going to be imported
This is about an easy way to create typical finance reports like Profit and Loss using DAX that (unlike all other solutions I’ve come across so far) can be handled with very basic knowledge of this language like this:
The trick that makes my solution so easy lies in the fact that it requires no aggregation functions of the output-mediums like:
pivot-tables: who struggle with asymetric logic and are not available in Power BI so far
cubefunctions: who are not available in PowerBI so far
So we have to build the details as well as all aggregations into the solution as it is and don’t rely on/use any aggregation functions (This means for Excel: We have to turn off subtotals as well as totals in our pivot tables. It means for Power BI: Hurray! Finally a solution where the lack of pivot-tables doesn’t matter).
The aim is to create a table/matrix with (account) details and aggregations into the rows and different slices of time-Intervalls or comparisons into the column sections. As for the columns, this will be covered by measures like [Actuals], [Budget], [PreviousPeriods], [Differences in all shapes…]. And – as the values in the columns should be the same – I’d prefer to use only one measure per column – that is fully sliceable and works on all (sub-) totals of course. … Ok – so some dreams later I found it:
So you just wrap simple measures like Act=SUM(Fact[Amount]), Plan=SUM(Plan[Amount]), DiffPlan_Act=[Plan]-[Act] … into the CALCULATE together with the bridge-table as the filter-argument:
This is the many2many-technique in it’s simplest form (PostFromMarcoRusso). It all goes via simple aggregation on all accounts found in the filter context:
Our bridge-table “AccountsAllocation” consists of one account number per simple account and has multiple rows for the (sub-)totals – being all accounts that belong to them:
The ConsKey stands for the row in our report (1) and the AccountKey_ holds the account numbers that are going to be aggregated (many (for the sub-totals) and 1 for the account-rows). So all we need is this unique and simple aggregation on AccountKey for every row in the report – with a filter from the Reports-table via our bridge table to the DimAccounts, who then filters our FactTables: 1 -> many -> 1 -> many.
His solution has features that would do my version good as well:
Directly connect to the model to be analysed without clumsy export of measures to txt via DAX Studio
Including calculated columns? No!: Who does calculated columns in DAX in PBI? Do them in the query editor using M instead (more functions, better compression, easier merge of model to SSAS once needed)
So wouldn’t it be cool if we could just add a documentation page to our current model – “all in one” so to speak? Here you find how to “hack”-connect with Excel to your current Power BI Desktop-Model.
So what works with Excel should work with PBI as well – just that we need to connect via the query-editor, using M. And of course: As we’re hacking ourselves here (i.e. the file we’re currently working on), we need to save our changes in order to make them being shown.
This DAX-VizArt-Wizard vizualizes dependencies between your DAX measures, shows the definition of all related measures and shows differences between the measures of 2 models/versions. This works for Power Pivot, Power BI and for Analysis Services Tabular (SSAS).
In the Power BI-Version you’ll see them in the Sankey-chart like this:
If select measures/nodes, all direct connections will be highlighted:
In the second version, all indirect connections will be highlighted as well & the selected measure definitions will be shown.
When reading horror-stories about Excel-hell describing how dangerous it is to use Excel in corporate environments, I cannot help but to think of this hilarious videodescribing the fatal consequences of acting without common sense: Just don’t do stupid things with it.
Although Excel comes nearly for free (in relation to what value it delivers) this doesn’t mean that you don’t need to invest in applying proper techniques (like in any other profession). There’s training and best practices for every different need.
But the best thing about Excel seems largely unknown still: Since the invention of Power Query it has never been easier to be save around Excel than before: A magic tool that can solve many of the problems that cause Excel hell: Repetitive tasks: The little adjustments and extensions that pile up when you use your workbook again and again and are often performed without realizing the (meanwhile complex) context of all the standard-Excel-elements involved: Power Query will prevent this mess. It will help you organize and automize your repetitive tasks in Excel.