Dynamically analyse water security outlooks using What If parameters in Power BI
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 you how to make a scenario analysis tool using Power BI What If parameters. 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, I've created some really cool PERCENTILEX calcs to analyse my data, but what if I wanted to calculate the percentile on the fly..? Can I even do that in Power BI?!?
DAX Magic:
Introducing What If parameters! They allow us to very quickly create a dynamic scenario analysis tool, by creating a calculated table, a measure and tying it together with a slicer. The trick is to then incorporate the 'What If' selected value into a DAX calculation.
We'll explore this by making a dynamic calculation of a selected percentile to evaluate the likelihood of our storage forecast volume:
Forecast scenario = PERCENTILEX.INC(Scenarios,[Total storage volume],[Forecast % Value])
🎦 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/
If you have an environmental DAX challenge that you’d like us to tackle in a future video then please write in the comments below or email the team at info@discoverei.com
Power BI Training Courses
If you’re interested in learning from our team, then we have a range of 2-day Power BI training courses available to fast-track your Power BI journey.
Click on the links below to learn more, and get in touch with the team today!
Purchase Training
Do you want to learn how to design engaging and intuitive Power BI reports, which communicate your key insights at a glance and tell your data story?
Our two-day Power BI training course is designed specifically for Power BI Professionals and Data Analysts, and provides our best practice tips, tricks and hacks to help you transform your data! We provide this course online, in-person group sessions, or customised in-house training for your team. Places are limited (max 8 participants per class) so secure your spot today!