M is the mash-up functional language of Power Query. Its formal name is Mashup or Power Query Formula Language, and it is used to query many data sources, while Data Analysis Expression (DAX) allows functions, much like Excel, to work on data stored in tables.
In this chapter, you will learn the differences between the two languages as we will look at the properties of both DAX and M. We will discuss the DAX syntax to understand how formulas are constructed, as well as look at how to add a DAX formula in Excel. Toward the end of the chapter, you will learn how to create a calculated column to display the result of a formula in a new column, and create a measure to calculate aggregates.
We will cover the following main topics in this chapter:
- Learning about the DAX and M functionality
- Constructing DAX syntax
- Creating a calculated column
- Creating calculated measures
It would be useful for to go through Chapter 9, Working with M, Chapter 10, Examples of M Usage, and Chapter 11, Creating a Basic Custom Function, prior to starting this chapter. The previous chapters contain valuable examples and discussions regarding the M language. You'll will find all the relevant code files used in this chapter at https://github.com/PacktPublishing/Learn-Power-Query.
For Code in Action video of this chapter please visit the following link: https://www.youtube.com/watch?v=up4CCRsNj7I&list=PLeLcvrwLe186O_GJEZs47WaZXwZjwTN83&index=13&t=0s.
DAX is mainly used in data transformations in Power BI dashboards where you would need to gain business analysis from existing data models. DAX is powerful when required to produce, for instance, growth percentage analysis across a list of products for specific date ranges, or to analyze market trends. Together with Power BI, DAX can powerfully assist with real-world business challenges to create meaningful, interactive business dashboards for reporting and decision-making. It is a formula language; it is not considered a programming language as it is structured using custom calculations (in fields and columns). As a formula language, DAX comprises an assembly of the following:
Now, M code is used in the Power Query editor. Each time you create a transformation, expressions are generated automatically. To view or edit any M code with the Power Query interface, you need to visit either the Advanced Editor window or the formula bar. We have already discussed and worked with M code in the other chapters of this section of the book.
In the following table, we have highlighted the main differences between the two language platforms as a summary:
Every language you come across will have a syntax and a structure. Syntax refers to all the elements that you use to construct a formula. A formula usually consists of functions. In Chapter 9, Working with M, in the Understanding the M syntax and writing with M section, you learned all about the M syntax. In this topic, you will learn about the DAX table and column name syntax.
It is important to note, especially if you are only just beginning to learn about DAX, that it is broken up into two parts:
You need to understand these tools as you will need to use the correct tool for the DAX code you are using. Often, we search for reference code and copy the code to adapt to our scenarios, so be mindful of this as you will most likely end up with an error and waste time trying to find out why the code does not work.
DAX functions and Excel functions are much the same in terms of behavior and type—for example, their array and lookup functions. The main difference between the two is the syntax. Excel uses cell references or ranges whereas DAX references a table or column in the formula. It should be noted that you cannot blend Excel functions and DAX functions in one formula. DAX will always require relationships to exist between tables when performing lookups.
Let's take a look at the DAX syntax elements. The following is an example of a basic DAX formula created in Power BI:
The following table explains each part of the DAX syntax:
If you do not enter the correct syntax, a syntax error will be returned in the column.
To create a DAX formula in Excel, you use the Power Pivot window. The following explanation assumes you know how to add Excel data to Power Pivot. This was explained in Chapter 2, Power Pivot Basics, Inadequacies, and Data Management. There are two ways to go about it:
- Click on the Power Pivot formula bar and start constructing the formula, as follows:
- Use the Design tab in the Power Pivot ribbon to access the fx (Insert Function) icon to select a function to use within the formula.
IntelliSense provides the user with a list of functions or parameters to help when constructing a DAX formula. This is the same as when we enter a function in Excel. The help tool is available to give us tips and offer help with functions by indicating what we need to make the formula work. This is the same for DAX formulas. By entering characters such as parentheses, quotes, or square or curly brackets, IntelliSense automatically creates the closing element for these characters.
IntelliSense also offers different types of elements to choose from by providing a drop-down list of elements. To the left of these elements, you will see an icon. The cube, triangle, square, and circle icons depict different things. The cube signals all the native M functions and the triangle, circle, and square are for all the variables, steps, parameters, constants, query names, and so on.
IntelliSense also highlights keywords and offers parameter hints; help is available at your fingertips:
Now that you understand DAX syntax and construction and know how IntelliSense can support formula construction, we will introduce you to DAX formula types.
There are a number of formulas that you would create in Power BI using DAX. These are all visible on the Power BI ribbon under the Calculations group—namely, New measure, Quick measure, New column, and New table, as shown:
Here is an explanation of the three main types of DAX formulas. The following calculations described are accessible via the Calculations group under the Home tab. Here, you will see New measure, Quick measure, New column, and New table:
- Calculated columns: These are used to perform calculations on rows automatically by adding a column or columns from different tables.
- Quick measures: These are built-in measure templates, which are already constructed for you and can be added without prior knowledge of coding.
- Calculated measures: These do not take up any physical memory and the results are more dynamic due to their adaptability. This type of calculation always requires an aggregator (function).
- Calculated tables: With this formula, you can create new tables and data.
DAX has a formula engine and a storage engine, which are responsible for running DAX queries within Power BI. In the following table, we have outlined the responsibility of each engine and a summary of the main advantages and disadvantages of each:
In this section, we will create a calculated column in Power BI Desktop to slice or filter a value or calculation on every row in a table. Let's run through the logic of what creating a calculated column means.
A calculated column is a new column that is added to an existing table. For each row of the table, the DAX formula is calculated immediately, just like using the autofill handle in Excel to fill in a formula. Be mindful of the fact that when using calculated columns, the result of the calculation is always stored in memory, unless of course it is reloaded or released when exiting or opening up Excel/Power BI. This will cause the table to be refreshed, which forces the column to recalculate. Let's see how this works:
- Open Power BI. For this example, we will be using the SSGThemePark.pbix file.
- You can create the DAX formula in any view, but I prefer the table view so that I can identify the data and see how the data is structured in the columns. It makes it easier when you can see column headings when working with formulas. The view you choose to create the DAX formula in is purely based on personal preference.
- Click on the New column icon in the Calculations group of the Home or Table tools tab. A new column is inserted to the right of the existing dataset, with a heading named Column. Notice that the formula bar is also active at this point, waiting for user input:
- To construct the DAX formula, simply type it into the formula bar. The formula bar is situated directly above the table headings. We will use an easy example to calculate the salary for each employee based on the hours worked multiplied by the hourly rate.
- The formula will be entered as follows. Change the Column text to SALARY so that the column heading is relevant to the calculation being performed on the column. After the = sign, start typing the name of the column that you would like to use; in this case, it will be the HRS column. Notice that once you start typing, the program automatically populates suggestions for you. Also, notice how the icons differ in the drop-down list according to the elements populated. In the following drop-down list, we can see function icons and table icons. Double-click on Table1[HRS] to include it in the formula:
- Add the multiplication operator (*), then type HOURLY RATE to end the DAX formula:
- This will now update the SALARY column, as shown in the preceding screenshot.
You have now learned how to create a calculated column using DAX formula construction. We will now look at the next type of DAX calculation—using measures.
In this section, we will learn when to use the calculated measure feature and how to create a new measure by adding a function or expression. Let's look at some of the things we need to know before creating a calculated measure.
All measures need to contain a function. A measure cannot work without the table column you are creating the measure on having a function (sum, min, count, and so on) within the formula—this is the difference between a calculated column and a calculated measure. This is called an aggregator (function), and without an aggregator, it is called a naked column in the programming world. IntelliSense is very useful here as it will complete your code for you.
The beauty of measures is that they are only calculated when they are accessed, and so they don't use up your memory. They also allow many different outputs to be produced by just changing the filter criteria of the existing measure. Measures are created in the Report or Data view in Power BI.
A measure can be applied across tables from one query to another with ease. It adapts just the same as an Excel formula would in a workbook when referencing cells. We can move measures without losing any functionality within the measure. When we create calculated columns, the data is stored in the xVelocity engine, so calculated columns take up more storage in your database. The virtual memory used in calculated columns is much smaller, however, when interacting through reports. Measures are faster to load as they are not stored in memory.
Before creating a calculated measure from scratch, let's have a look at the Quick measure feature, which provides built-in, readily created formula templates for use.
We can use measures that are built for us, or we can create our own measure constructions using DAX formulas. If you are a beginner, the best method is to use the Quick measure feature, which returns a list of common calculations for you to choose from and apply to your dataset. This will help you to understand DAX formulas and become familiar with the DAX structure.
Take the following steps to test this measure:
- Open the dataset you wish to perform the measure on. We will use SalesData.pbix for this example.
- From the Home tab, locate the Quick measure icon and select it, or right-click on an existing table and select Quick measure:
- The Quick measures dialog box will populate, where you can select from a list of calculations to apply to your table.
- Select Year-to-date total for this example.
- Drag the fields from the table that you wish to use in the calculation. For this example, we will drag the Sum of TOTAL SALES field from the Fields list into the space provided under the Base value setting:
- Notice how you can change the calculation type by clicking on the drop-down arrow to the right of the box, as in the following screenshot:
- Drag the DATE SOLD field from the Fields list into the space provided under the Date setting.
- Click on OK to let Power BI work out the DAX function for you.
The measure is added to the Fields list pane:
- Drag the TOTAL SALES YTD measure onto the existing table dashboard. The table is updated with the new column reflecting the YTD:
- Click in the formula bar to view the DAX formula that was created by running through the previous few steps in the user interface:
As we will be calculating the sum of the sales by quarter, we will need the Filter function to divide the quarter into categories. After we have done this, the formula will calculate the total sales according to the quarter categories. The reason for using this measure is that a quarter can have new salary information refreshed at any time, thereby increasing the number of rows.
Let's create a new measure to add to our report dashboard:
- Using the example file from the previous example, SalesData.pbix, we will add the TOTAL SALES field to the dashboard as a Card visualization type (just next to the existing table). Use the Format icon to change the Data label setting to None to display the total sales for the entire dataset. This is done to show you that there is no filter applied to this field:
- Resize the total sales card so that it does not take up the entire screen.
- Add the Year, Region, and TOTAL SALES fields from the Fields list into the white space of the dashboard to create a filter. This is done to show you that we can create filters without DAX formulas, too:
- Next, we will look at how to work out the total sales per season. Make sure you have clicked on the Reports icon in the navigation pane.
- Drag the Season and TOTAL SALES fields onto the dashboard. This automatically creates a filter on the Season column for you. Although this is easier than creating a measure, it would be better practice to do this using a measure as you would need to use measures as expressions when building a DAX formula:
Let's do the same thing here, but by using a measure instead.
- Select the table you want to use for the measure before creating the measure; otherwise, it will select the first table by default. Note that this can be changed later, but it is easier if you select the table beforehand.
- In the Power BI interface, select New measure. We will be typing the following code into the formula bar in order to work out the total sales for each season using a filter and a calculation:
Note that if you do not have an aggregator (function) in your code, then you will not be able to create a measure—IntelliSense will watch you while you work and will not offer you the field name in order to go further with the code if you omit the function. If you don't see the field populate in the auto-complete pane when constructing your code, then it is probably because you do not have the correct conditions in place to create the measure.
When you start typing the code, you will be offered suggestions (through IntelliSense) based on your input. To navigate through the list offered, simply use the down arrow on the keyboard and then the Tab key to select the element. Alternatively, just double-click on an element to add it to the code.
To add a new line in your code, press Shift + Enter on the keyboard. So, now you know a few things about entering code.
- If you now drag the TOTAL SALES SUM measure from the Fields pane onto the table, you will notice that the values are exactly the same. The underlying difference is that the measure will remain the same, whereas the TOTAL SALES column could be renamed or removed at some stage and cause problems with our formula in the future.
- We can remove the TOTAL SALES column from our visualization now as we have put the measure in place.
- Now, we will use the CALCULATE function to calculate the sum of our quarters. When using the CALCULATE function, we need to have a measure—we cannot use the table and column references.
- Type the measure name, then =, followed by the CALCULATE function, which will require an expression (a measure) within parentheses. Refer to the following screenshot to see how this should look:
- Drag the CALQ measure from the Fields list onto the table dashboard:
- CALCULATE is really powerful when you add the FILTER function to it. We will now combine the FILTER function with the CALCULATE function to filter the Season field for Spring only:
- Now, drag CALWFILTER from the Fields list onto the table dashboard. Notice how only Spring has a value now.
- Add the Year field to the table dashboard:
- The filter now works for 2018 and 2019. You can use the sort drop-down arrow on the Year column to sort it in descending or ascending order to see the results.
I hope this gives you some idea of what is possible with DAX formula measures. Next, we will move on to organizing measures.
We can place all our created measures into a display folder in the folder list pane. All of our measures can be stored in one folder, even if they are associated with different tables:
- Click on the Model icon in the navigation pane.
- Select the measure you wish to move into a display folder:
- The Properties pane will open to the left of the Fields list.
- In the Display folder area, type the name of the folder you wish to place the measure into.
If you want to create a subfolder, then type a backslash (\) after the display folder name entered into the area provided—for instance, Measures\Sales.
In this final chapter of the book, you have learned all about the differences between DAX and M functionality. You will now be confident in identifying the different parts of the DAX formula syntax structure and should be knowledgeable about where to construct DAX formula in Excel, Power Query, and Power BI.
We also touched on IntelliSense, which aids in DAX formula construction, and we know about the three main types of DAX formula (which are calculated columns, calculated measures, and calculated tables). You can create a calculated column, as well as quick measures and calculated measures. You can also create folders in which to store measures in the Field list of the Power BI interface. Measures will help you construct an expression formula to calculate a result specific to a purpose, dynamically and in real-time.
We hope that you now have a wealth of knowledge after reading this book and that it has instilled a desire to learn more about this extremely powerful, in-depth application named Power Query.