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:
The measure “Amount Shipped” will get rid of its USERELATIONSHIP, using a simple filter instead:
“Active Orders” are even simpler, no X-functions any more:
Admittedly, this is not a huge change, but I’m feeling much more comfortable with it and also have the feeling, that further adjustments to the model might be easier to make (like the duration-calculation for example in the following post).
Simplify_EventsDuration_SQLBI.zip
So enjoy & stay queryious 🙂