Using DAX

The real power of Power BI models is in calculations using the DAX language. While many Power BI users focus on the model and try to avoid DAX entirely, anything that goes beyond simple, basic aggregations of data requires DAX calculations. And you will surely encounter the need for more sophisticated calculations in Power BI sooner rather than later. The typical way things go is that the first well-designed Power BI report leads to more and ever more complicated questions to ask about your data.

Part 2 of this book aims to give you inspiration on what can be achieved using DAX and how to approach a business problem with DAX. Before we dive into Part 2's scenarios, we still have some fundamentals to cover. In this chapter, we briefly touch upon the different uses of DAX in Power BI. These uses are:

• Calculated columns
• Calculated tables
• Measures
• Security filters
• DAX queries

We also discuss how to create date tables with...

Calculated columns

A calculated column is a column of data that is added to a table in the Power BI model by performing a DAX calculation. A basic example is to calculate the value of a sales transaction by multiplying the number of products sold by the price per product (note that column names are written between square brackets in DAX):

    Amount = [Quantity] * [Price]

Below is the resulting calculated column:

Figure 1.3.1: A calculated column

Calculated columns are a straightforward way to add some intelligence to the Power BI model. If you come from an Excel background, this probably has the most natural feel to it, as putting formulas in columns is the way most Excel users have learned to work in Excel. But our strong recommendation is: DO NOT use calculated columns, unless you have very good reasons to do so. There are a couple of reasons why:

• A calculated column creates new data, which takes up space in the model. As was discussed in the previous...

Calculated tables

Calculated tables are comparable to calculated columns: they add data to a Power BI model, but now in the form of a complete table. To create a calculated table, you most often need special DAX table functions. You will encounter many DAX table functions in Part 2; for a general introduction to table functions, see Chapter 1.4, Context and Filtering.

To create a simple calculated table in a Power BI model, you can use the table constructor. The expression below, consisting only of a list of values between braces, creates a table with one column:

Example = {1, 2, 3}

The result of this formula is a table named Example, with a single column of [Value]:

Figure 1.3.2: A calculated table made with the table constructor

Note that the table constructor does not give much control over the table that is created. The column is named Value and the data type of the Value column is derived from the values provided (which is, of course, fairly accurate...

Measures

Measures, or in some earlier model versions, calculated fields, are without a doubt the most powerful element of Power BI models. In fact, most of the work we do on Power BI models comes down to designing and implementing DAX measures.

When using a numeric column from a fact table in a Power BI report, the column values will be aggregated. The basic aggregations available are sum, average, minimum, maximum, count, distinct count, and some statistical aggregations like standard deviation, variance, and median. The basic aggregations differ depending on the data type; for a Date column, for instance, you can choose earliest and latest, count, and distinct count only. When you use columns this way, the Power BI model creates an implicit measure in the background: an aggregation function that returns the selected aggregation of the values in the column.

In real life, many of the required insights come down to aggregations that cannot be expressed in terms of basic aggregations...

DAX security filters

DAX can also be used for implementing security within a Power BI model. When a user retrieves a report, they will be able to see all results provided by the model through that report. In many cases, there is a need to restrict what the user sees, based on their role or identity. For instance, consider the DAX security expression below:

Customer[Region] = "Europe"

When set for a specific security role, this DAX security filter will cause users in that role to only see customers in the Europe region, together with data related to those customers.

Chapter 2.1 further introduces security with DAX.

DAX queries

The last way to use DAX is as a query language. You will not need this when working with Power BI visual reports, but classic, RDBMS-oriented reporting tools mostly rely on retrieving custom datasets from databases to render reports. A common data source for these is a data warehouse or other database; but a Power BI model in the form of a published Power BI dataset can be used in this way as well. Note that at the time of writing, you need to have a Power BI Premium license, either per capacity or per user, to do this.

A specific use case for DAX queries is in Power BI paginated reports. These are developed using the Power BI Report Builder (as opposed to Power BI Desktop, which is the tool for all other use cases) and can connect to a published Power BI model.

When making the connection, you need to provide a DAX query to retrieve a set of data from the Power BI model:

Figure 1.3.4: Writing a DAX query in the Power BI Report Builder

If you use Power...

Date tables

Most, if not all, Power BI models contain data that is related to dates. A date table (or calendar table, or whatever you like to call it) is therefore a common ingredient of a Power BI model. A date table has a special place in the model, because of DAX time intelligence functions (more on these in Chapter 1.4, Context and Filtering).

A date table must have one row per day for an uninterrupted period of time. Typically, this period should be large enough to cover all the rows in your fact tables. It is advisable to start and end the date table on year start and end, respectively. The date table must have a date column, being the unique key of the table (you may choose the name of this column yourself). Other columns in the table are attributes for each day, like year, month, quarter, weekday, and so on.

Power BI models have an Auto date/time feature which, when turned on, creates a hidden date table for each column in the model that has the date or date...

Best practices in DAX

When working with DAX, you will benefit from following some best practices. Applying these will help you create fast models, make it easier to maintain your models in the long term, and allow you to better support others who create Power BI reports or other output from your models.

Think in terms of DAX measures primarily

If we did not make ourselves clear enough above: your main DAX tool should be DAX measures. These are highly dynamic, do not make the model larger, and there is no calculation you cannot do through a measure.

As a rule of thumb, calculated columns and calculated tables are a no-go, until you have very good arguments for using them!

Build explicit measures

We recommend creating explicit DAX measures instead of using numeric columns from (fact) tables directly in visual reports. There are several reasons for this:

• A measure is created by the Power BI model anyway when you use a column in a report, and it is easy...

Summary

In this chapter, you have seen the different uses of DAX in Power BI models: calculated columns, calculated tables, measures, security rules, and queries. The main takeaway is that DAX measures are (or should be) the primary way of generating valuable results from a model, and indeed, for the majority of the remainder of this book we will focus on DAX measures. We have given you some best practices for working with DAX: avoid calculated columns, use explicit DAX measures, create simple DAX measures and use these as building blocks for more advanced calculations, use measure tables, and hide elements of a model that could confuse the report designer (even if that is yourself).

The next chapter deals with probably the most important concepts to understand when working with DAX: context and filtering. After that, we will be ready to explore advanced DAX business cases in Part 2.