Power BI – The Data Model – An Introduction to DAX

Hi there, now after our last lesson where we looked in detail at base aggregate measures, in this lesson we’re going to look at some of the functions which are available to us in DAX which is the code that we use to create our measures and our calculated columns. So what is DAX?

 DAX stands for Data Analysis Expressions and as we’ve already seen in the last lesson, in many ways it’s very similar to the codes that we use in Excel day in, day out.

Many of the functions that we know and love from Excel have made the transition from Excel into Power BI. Some of them are exactly the same, such as the SUM, COUNT and AVERAGE functions, while others like the VLOOKUP function are there but called a different name and have been tweaked.  There’s then a whole new suite of functions that have been created to give us enormous scope for creating incredibly powerful insights.

DAX as we’ve seen, is used in Calculated Columns, Calculated tables and measures.

Now at the same time while DAX is very similar to some Excel formulas, it can also be more complicated to get right. That’s because 1) we need to consider several different types of contexts (one of which is filter contexts which we’ve already started to learn about) and because 2) we frequently write measures which incorporate information from several different datasets.

We’ve already started to have a look at DAX in both our calculated columns and measures and we’ve started to use DAX syntax without knowing it (after all we know now to put report names in apostrophes and columns in square brackets). But measures are the big beast in Power BI. They’re what makes Power BI so powerful and so we’re going to use these to do the vast amount of our DAX examples in Power BI.

A quick heads up though – this course is not an intensive DAX course. While we do talk through some key functions in detail, we’re also going to take advantage of the quick measure function to compile a lot of our measures. The Quick Measure function is relatively new but it is improving all the time and we’ll find that it does of the heavy lifting for us and will more than get you and a long way into Power BI. Not to mention that until you get really good at DAX it’s quite a bit faster too!

There are hundreds of DAX functions – enough for a DAX course in itself in fact, so let’s have a quick look at them!

Switch to Microsoft website https://docs.microsoft.com/en-us/dax/dax-function-reference

So here we are at the Microsoft Dax webpage and here on the left hand side we can see the different types of measures that DAX offers split into different subfolders much like M code was.

Now these text ones at the bottom here, if I select the drop down menu then we’ll see a lot of functions that look familiar – CONCATENATE, FIND, LEFT, LEN and RIGHT           , UPPER, LOWER, TRIM and so on: these are all functions that we’d find in Excel and work in pretty much the same way with the main principal differences being that they do not use cell coordinates, they use row context which we’ll talk about in the next lesson. To understand what a function does, then click on the link such as this one, the LEFT function and MSDN will give you a description of the syntax of the code, the parameters, the information returned when the function is evaluated and generally an example of the function in use.

 In the LOGICAL functions here – again weve got some pretty familiar functions – IF, AND, OR, IFERROR, all of these are Excel functions as well.

However there are some brand new functions such as in most of this section here, the Time Intelligence section. Most of the functinos here are almost entirely new and they allow us to do some brilliant things like easy comparing totals year over year, YTD, QTD, MTD as well as performing cumulative calculations. These functions are really here to address the need that analysts have to analyse and compare information over certain time periods and there’s nothing really like them in standard Excel.

We’ll be going over some of the more key functions in this pack either creating our own explicit measures or using the quick measure tool and by the end of the course you will have a competent grasp of:

• Base measures
• Derivative measures
• Filter context
• Row context
• Scalar functions
• Table functions and their use in measures
• The CALCULATE function
• Variables

A good grasp of these concepts should mean that you should be able to write the vast majority of DAX measures that you’ll need in your every day work with Power BI.