How to analyse dam storage volumes in Power BI using DAX Iterators
This video is part of DiscoverEI's DAX for Enviro's series, and walks through an example of how to use Power BI for water resource management. We show how to analyse total system dam storage volumes across 120 different modelling scenarios in Power BI using the DAX iterators: AVERAGEX, MINX and MAXX. We take it to the next level by configuring error bands for the minimum and maximum storage outlook volumes to show the range of possible scenarios. Watch the video to learn about how to apply these DAX functions and play around with the Power BI report that we created in the video, embedded below.
The Scenario:
Let's imagine that I am a water resources manager of a small town where the primary water supply comes from 3 reservoirs. I have run 120 different scenarios in my water resources model (eWater Source) to provide an indication of what the next 5 year storage volume could look like. I want to analyse the forecast data for an individual reservoir, as well as for the total storage volume. I've imported the data into Power BI - but I'm stuck? What are my options?
DAX Magic:
Introducing DAX Iterators! Iterators in DAX are functions that iterate through all rows of the given table, apply the expression, and then aggregate the result. The first step is to create a simple measure to sum up the total system storage volume:
Total storage volume = SUM('Water Outlook Forecasts'[Storage Volume (ML)])
And then, we can iterate this calculation across all of our 120 scenarios for the statistic of our choice (try AVERAGEX):
Average total forecast storage volume = AVERAGEX(Scenarios,[Total storage volume])
Try using MINX and MAXX to get the envelope of the storage volume ranges.
🎦 Check out the video 🎦
Recommended DAX Resources
As mentioned in the recording there are some AMAZING resources out there to help you with your DAX journey. Here is our pick of the bunch:
Power BI Docs: https://docs.microsoft.com/en-us/dax/dax-overview
DAX Guide: https://dax.guide
SQL BI: https://www.sqlbi.com/
RADACAD: https://radacad.com/new-video-course-dax-and-calculations-in-power-bi
Matt Allington: https://exceleratorbi.com.au/blog/
Chris Webb: https://blog.crossjoin.co.uk/
DAX Formatter: https://www.daxformatter.com/
DAX Studio: https://daxstudio.org/
DAX.Do: https://dax.do/