Alex Beech

Consultant at 3Cloud

Setting Variables Using DAX in Power BI

Welcome to another Azure Every Day focused on one of my favorite topics, using DAX in Power BI. In a previous post, I covered a couple of my favorite DAX formulas, CALCULATE and FILTER. Here, I’ll discuss variables and how to work those into CALCULATE/FILTER to expand on that and make it even more powerful. You may want to check out my previous post here, as I’ll be continuing using that demo.

• I started by building in a CALCULATE/FILTER function in a table to calculate the beginning balance for 2017 for all my assets.
• My code (see my video demo for code detail) tells it to calculate the sum of the beginning balance and I filtered the table where fiscal year equals 2017, and finance type equals assets.
• Now, let’s say we want to know the assets for every year, not just 2017. To do this, we need to set the year into a variable and then it will calculate that asset for each individual year.
• You use the VAR function to set the variable in the code and you’ll need to give it a name. In my case I’ll use YR for year and I’ll have that EQUAL to the fiscal year. It’s important to note that anytime you set a variable, you must hit return at the end.
• Next, I’m going to update my FILTER. My code is calculating the sum of the beginning balance and I’m filtering the table where fiscal year equals 2017, but now I want to take that out and change it to fiscal year equals year.
• How this works is when this goes through each row it will calculate for each year by using the variable instead of hard coding the year into there.
• So previously we only had one outcome for 2017, now when we submit this, we’ll see four outcomes as we have four years’ worth of data, so we’re getting a calculation for our beginning balance for each year.
• We can even step this up a bit if we wanted not only the beginning balance for each year, but also for each finance type. Maybe we don’t just want the assets but other values like equity, expense or liability.
• All we need to do is set another variable that I’ll call FT for finance type. And instead of doing this for where finance type equals asset, we’ll say where finance type equals our variable.
• Now we’ll have the calculation for every year for every individual finance type.

I hope this quick example helps you to start using these DAX formulas in your reports. The CALCULATE/FILTER functions and using variables are something I use all the time in Power BI.

Setting Variables Using DAX in Power BI

DAX CALCULATE and FILTER Functions in Power BI

We all know Power BI is a powerful analytics tool and it’s easy to create a new Power BI Desktop file, import some data into it and create reports that show valuable insight. Adding Data Analysis Expression, better known as DAX, enables you to get the most out of your data.
If you’re new to Power BI or DAX, DAX is a collection of formulas, or expressions, that are used to calculate and return one or more values. DAX helps you to use data already in your model and create new information from it that can solve real business problems.
When I first started using DAX functions, it brought my Power BI skills to the next level. I was able to tackle some analytical needs that I had struggled with in the past. I’m here to share a couple favorite formulas that I use all the time called the CALCULATE function and the FILTER function. Please be sure to watch my video included in this post as I walk through using this DAX formula.
• In my demo, I’m working with a data set to find the beginning balance for 2017 for our assets.
• To do that I need to sum a column in my table called beginning balance when fiscal year equals 2017 and when financial type equals asset.
• I’ll do this by using a combination of the CALCULATE function and the FILTER function. The CALCULATE function allows you to calculate a function on the entire table.
• In my code I’m going to CALCULATE the sum on our beginning balance. This would calculate the sum for the entire table.
• But we only want to calculate the sum for 2017 for just the assets and financial type. For this, once we have calculated the table, we need filter that table. Think of this FILTER function as making a digital table in the background.
• We need to FILTER it where fiscal year equals 2017 and where finance type equals asset. In my code, I’ll add FILTER for the function, and we need to tell it what table we are going to be filtering, in my case it’s the balance table. Then add where fiscal year equals 2017 and where finance type equals asset.
• Using these DAX functions, our result will show the beginning balance for our assets for 2017.
• My video shows you exactly how to write the code I used here, so be sure to check it out.

As you can see, this is super simple, and this formula allows you flexibility in how you write it. You can FILTER tables in many ways and use different functions within CALULATE. I hope you enjoyed this simple use case of these powerful DAX functions in Power BI.