Power BI – The Data Model – An Introduction to DAX

An Introduction to DAX

Previous Session Summary

In the previous lesson, we covered how to enter the PowerQuery editor for the first time by highting some Purchase Order Data in our current workbook and then going to the Data tab and then going to From Table/Range.
We then looked at the different components of the PowerQuery editor being the Preview Screen, the Query Pane, the Tabs and Ribbon and the Query Settings Pane.

Lesson Synopsis

In this lesson we start looking at some of the basic steps that we or PowerQuery automatically use to transform our source data from its original ‘dirty’ state, to clean columnar data. Now before we start off in this lesson, one very simple, important point:
You cannot edit individual cells in the PowerQuery Editor. You can only perform transformations on whole columns, rows or tables.
In this lesson then we’re going to continue working with the PO data from the last lesson. We’re going to look at a couple of transformation steps which PowerQuery has added automatically and then we’re going to put in a few of our own, including removing some unnecessary rows, promoting the top row into the Header row and then loading our nice, neat columnar data into Excel.

Resource Listing

The Video

Play Video

Lesson Notes

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.

.... and a little more!

The Formula Bar – The Formula Bar does not automatically come up in the PowerQuery Editor the first time that you enter the Editor in Excel. To bring the Formula Bar up, go to the View tab. In the Layout section of the Ribbon, tick the Formula Bar box.

Remove rows – While we remove the unnecessary top rows in the video, many system reports also have totals lines at the bottom of the reports which can cause a problem when we’re doing things like summing our data later once we’re finished in PowerQuery. We also have the option to remove a specific number of bottom rows using the Remove Rows drop down in the Home tab, as well as removing alternative rows, duplicate rows, rows with errors and blank rows.

Use First Row as Headers – In the video we promote the first row in the table into the headers row. However we can also take the Headers and put them into the first row. To do this go to the same Use First Row as Headers drop down in the Home or Transform tab and select Use Headers as First Row.