From the course: Excel Power Tools for FP&A: Power Query and PowerPivot

DAX rules and syntax

From the course: Excel Power Tools for FP&A: Power Query and PowerPivot

Start my 1-month free trial Buy for my team

DAX rules and syntax

“

- DAX stands for Data Analysis Expressions. The introduction of DAX has made Excel and other Microsoft software increasingly more powerful as scalable tools for data analysis and business modeling. DAX is a Microsoft formula language that can be used across a wide array of applications. We're going to go through its usage in Power Pivot, but it can also be used in Power BI, Azure, and Tabular modeling with SQL. Now, before you get anxious about learning a new language, let me ease your concerns. DAX is a formula language, it's not a programming language, so you don't have to be a programmer to understand how to use it. You're not going to have to learn how to write code or understand syntax that's completely unfamiliar to you. The good news is DAX has a very similar structure in syntax to the formulas that you frequently write in Excel. It can be used to write custom calculations called measures and calculated columns. Those can then be used to perform relational data calculations and aggregations across entire columns of data, and they perform at scale, handling very large volumes of data across many tables and data sets. Just like in Excel, a DAX formula always starts with an equal sign, and then after the equal sign, you write an expression that returns a value. Let's go back to one of our earlier files in this course. Here I have a dynamic table for all of my transactions year-to-date. What I want to be able to do over to the right is to create a DAX formula that determines my item price, based upon item cost here in column K and item markup in column L. For illustrative purposes, I'm going to insert this into column O. I'm going to type in the name of this dynamic table, which is transactions year-to-date, tab, and then open bracket. When I do this, it calls on all of the various elements I might consider. I could certainly call on any column. I could call on fuller elements like all of the table, just the data, only the headers or the totals, but then take a look at this option. In Microsoft 365, I have the ability to call on an adjacent row within a range of data. So I'm going to select this, @, and then I want to call on item cost. I'm going to close my brackets, and now let's take a look at what it's done. It's taking a look at the dynamic table, the adjacent row in the column item cost. It's highlighting this box right here at K2. Next, I'm going to multiply it by one plus the name of my table, open bracket, this row. But this row where? I want it to take a look at the item markup column. Let's close up those brackets and close the parentheses. So what this formula is going to do is it's going to look at my dynamic table, this location in this row for item cost times one plus this location in the item markup. Let's see what we get. $233.32. If we want to QC this, I'm going to go equals K2 times one plus L2. I should get the same answer and I do. As you can see, we can use DAX in Excel and apply them to dynamic tables. I use this all the time in my own business modeling. But we can also use DAX in Power Pivot, using something called measures. Measures allow me to create a formula that I can then use again and again throughout all of my data sets. Here you can see I've created a measure for cost, gross margin, gross profit, and revenue using the DAX language over here to the right. This is the power of DAX. Instead of writing dozens, maybe even hundreds of different formulas throughout our models, we can write one, or just a few, and they adapt to the analysis and context of our data. That's what makes DAX so scalable, especially in FPNA, where we're often slicing the same metrics in many different ways. DAX is an incredibly powerful way to get your data working for you, and like Excel, it rewards clarity, logic, and structure. And trust me, once you get the hang of it, you may even start favoring it over many traditional Excel functions.

Contents