Introduction to DAX

Learn how to create and work with DAX from an established expert.

You will learn how to...

Introduction to DAX

Welcome to the Power BI Guided Learning section designed to introduce you to DAX.

DAX stands for Data Analysis Expressions, and it is the formula language used throughout Power BI (it is also used by Power BI behind the scenes). DAX is also found in other offerings from Microsoft, such as Power Pivot and SSAS Tabular, but this collection of Guided Learning topics focuses on how DAX is used - and can be used by you - in Power BI.

DAX and this Guided Learning video series

The goal of this Guided Learning section is to teach you DAX basics and fundamentals - how to think about DAX, how it works, and the most useful features as explained (and learned with lots of experience) by a renowned DAX expert, Alberto Ferrari.

The videos in this Guided Learning section on DAX teaches you DAX basics from the perspective of how the DAX formula language works. This is useful when creating DAX formulas from scratch, but it's also very useful for understanding how Power BI creates those DAX formulas as you create queries in the Query Editor.

In this video - introduction to DAX

DAX concepts are simple and straightforward, but DAX is powerful. DAX uses some unique programming concepts and patterns which can make it difficult to fully use and understand. Traditional ways of learning languages may not be the best approach to DAX, so the goal of this video is to teach you concepts and theory that will help later on in your Power BI work.

DAX is a functional language, which means the full executed code is contained inside a function.

In DAX, functions can contain other, nested functions, conditional statements, and value references. Execution in DAX starts from the innermost function or parameter, and works outward. In Power BI, DAX formulas are written in a single line, so formatting your functions correctly is important for readability.

DAX is designed to work with tables, so it has just two primary data types: Numeric and Other. Numeric can include integers, decimals, and currency. Other can include strings and binary objects. This means that if you build your DAX function to work on one type of number, you can be assured that it will work on any other Numeric data.

DAX uses operator overloading, which means that you can mix data types in your calculations and the results will change based on the type of data used in the inputs. Conversion happens automatically. This means you don't have to know the data types of the columns you're working with in Power BI, but it also means that sometimes conversion can happen in unexpected ways. It's good practice to understand the data you are using to ensure that your operators are behaving as anticipated.

There is one data type in particular that you'll likely be working with a lot in Power BI: DateTime. DateTime is stored as a floating point value with both integer and decimal parts. DateTime can be used accurately for calculations of any time period after March 1, 1900.

Video content courtesy of Alberto Ferrari, SQLBI

DAX calculation types

There are two primary calculations you can create using DAX:

  • calculated columns
  • calculated measures

Before digging into creating either of those, it's good to have a firm grasp on DAX syntax for tables and columns, which you will use when creating either calculated columns or calculated measures.

DAX table and column name syntax

Whether you're creating a new column or measure, it's important to know the general format of table names in DAX:

'Table Name'[ColumnName]

If there are spaces in the table name (as shown above), the single quotes around the table name are mandatory. If the table name has no spaces, the single quotes can be omitted, so the syntax looks like the following:

TableName[ColumnName]

The following image shows a DAX formula being created in Power BI:

You can also omit the table name completely and just use the column name, but this is poor practice for writing clear functions (and thus, for clear DAX code). Column names must always include the square brackets.

It's best practice to always do the following:

  • No spaces in table names
  • Always include the table name in formulas (don't omit it, even though DAX lets you)

Creating calculated columns

Calculated columns are useful when you want to slice or filter on the value, or if you want a calculation for every row in your table.

You can create calculated columns in Power BI Desktop by selecting New Column from the Modeling tab. It's best to be in Data view (rather than Report or Relationships view), since you can see the new column created and the Formula Bar is populated and ready for your DAX formula.

Once you select the New Column button, the Formula Bar is populated with a basic column name (which you change to suit your formula, of course) and the = operator, and the new column appears in the data grid, as shown in the following image.

The required elements for a calculated column are the following:

  • a new column name
  • at least one function or expression

If you reference a table or column in your calculated column formula, you do not need to specify a row in the table - Power BI calculates the column for the current row for each calculation.

Creating calculated measures

Use a calculated measure when you are calculating percentages or ratios, or you need complex aggregations. To create a measure using a DAX formula, select the New Measure button from the Modeling tab. Again, it's best to be in the Data view of Power BI Desktop since it shows the Formula Bar and makes it easy to write your DAX formula.

With measures, you see a new measure icon appear in the Fields pane with the name of the measure. The Formula Bar is again populated with the name of your DAX formula (this time, with your measure).

The required elements for a calculated measure are the same as they are for a calculated column:

  • a new measure name
  • at least one function or expression

Video content courtesy of Alberto Ferrari, SQLBI

DAX functions

With DAX, there are many functions available to shape, form, or otherwise analyze your data. These functions can be grouped into a handful of categories:

  • Aggregation functions
  • Counting functions
  • Logical functions
  • Information functions
  • Text functions
  • Date functions

Similar to Excel, when you start typing your formula into the Power BI Desktop Formula Bar, a list of available functions appears to help you determine which available function you want to select. And by using the up and down arrow keys on your keyboard, you can highlight any of the available functions, and a brief description is displayed.

Power BI displays the functions that match the letters you've typed so far, so if you type S only functions that begin with S appear in the list. If you type Su, only functions that contain the letter sequence Su in their name appear in the list (they don't have to start with Su, they just have to contain that letter sequence).

It's easy to experiment with DAX in this way, and to find each of the various DAX functions that are available in Power BI. All you have to do is start typing, and Power BI helps you along.

Now that we know how to get that DAX formula started, let's take a look at each of these function categories in turn.

Aggregation functions

DAX has a number of aggregation functions, including the following commonly used functions:

  • SUM
  • AVERAGE
  • MIN
  • MAX
  • SUMX (and other X functions)

These functions work only on numeric columns, and generally can aggregate only one column at a time.

However, special aggregation functions that end in X, such as SUMX, can work on multiple columns. These functions iterate through the table, and evaluate the expression for each row.

Counting functions

Often-used counting functions in DAX include the following:

  • COUNT
  • COUNTA
  • COUNTBLANK
  • COUNTROWS
  • DISTINCTCOUNT

These functions count different elements, such as distinct values, non-empty values, and table rows.

Logical functions

The collection of logical functions in DAX include:

  • AND
  • OR
  • NOT
  • IF
  • IFERROR

These special functions can also be expressed with operators. For example, AND can be typed as (replaced with) && in your DAX formula.

You can use operators (such as &&) when you need more than two conditions in your formula, but otherwise, it's best practice use the function name itself (such as AND) for readability of your DAX code.

Information functions

Information functions in DAX include:

  • ISBLANK
  • ISNUMBER
  • ISTEXT
  • ISNONTEXT
  • ISERROR

While these functions can be situationally useful, there is value in knowing the data type of your columns ahead of time, rather than depending on these functions to provide the data type.

DAX uses the MAX and MIN functions to both aggregate values, and to compare values.

Text functions

The text functions in DAX include the following:

  • CONCATENTATE
  • REPLACE
  • SEARCH
  • UPPER
  • FIXED

These text work very similarly to the Excel functions that have the same name, so if you're familiar with how Excel handles text functions, you're already a step ahead. If not, you can always experiment with these functions in Power BI, and learn more about how they behave.

Date functions

DAX includes the following Date functions:

  • DATE
  • HOUR
  • NOW
  • EOMONTH
  • WEEKDAY

While these functions are useful to calculate and extract information from date values, they do not apply to time intelligence, which uses a date table.

Video content courtesy of Alberto Ferrari, SQLBI

Using variables in DAX expressions

Using variables are an extremely powerful part of a DAX expression.

You can define a variable anywhere in a DAX expression, using the following syntax:

VARNAME = RETURNEDVALUE

Variables can be any data type, including entire tables.

Keep in mind that each time you reference a variable in your DAX expression, Power BI must recalculate its value according to your definition. For this reason, it's good practice to avoid repeating variables in your function.

Video content courtesy of Alberto Ferrari, SQLBI

Table relationships and DAX

Power BI lets you create relationships among multiple tables, including tables that come from completely different data sources. You can see those relationships for any data model in the Relationships view of Power BI Desktop.

DAX relational functions

DAX has relational functions that enable you to interact with tables that have established relationships.

You can return the value of a column, or you can return all rows in a relationship using DAX functions.

For example, the TABLE function follows relationships and returns the value of a column, while RELATEDTABLE follows relationships, and returns an entire table that is filtered to include only related rows.

The RELATED function works on many-to-one relationships, while RELATEDTABLE is for one-to-many relationships.

You can use relational functions to build expressions that include values across multiple tables. DAX will return a result with these functions, regardless of the length of the chain of the relationship.

Video content courtesy of Alberto Ferrari, SQLBI

DAX tables and filtering

One significant difference between DAX and the Excel formula language is that DAX allows you to pass entire tables between expressions, rather than being constrained to a single value. One powerful effect is that DAX allows you to filter tables in its expressions, then work with the filtered set of values.

With DAX, you can create entirely new calculated tables and then treat them like any other table - including creating relationships between them and other tables in your data model.

DAX table functions

DAX has a rich set of table functions, including the following:

  • FILTER
  • ALL
  • VALUES
  • DISTINCT
  • RELATEDTABLE

These functions return a full table rather, rather than a value. Typically you'll use the results of a table function in further analysis as part of a greater expression, rather than using that returned table a final value. It's important to note that When you use a table function, the results inherit the relationships of their columns.

You can mix table functions in your expression, as long as each function uses a table and returns a table. For example, consider the following DAX expression:

FILTER (ALL (Table), Condition)

That expression would put a filter over the entirety of Table, ignoring any current filter content.

The DISTINCT function returns the distinct values of a column that are also visible in the current context. So touse the above DAX expression example, using ALL in that expression ignores filters, while replacing ALL with DISTINCT would observe them.

Counting values with DAX

One common question that Power BI report builders want to answer is the following:

  • How many values do I have for this column?

That may be a simple question to answer with a table displayed in front of you, but DAX approaches in a different way in a different way, particularly when there's a relationship between tables.

For example, Power BI and DAX includes values that are not properly cross-indexed. If the incoming relationship is broken, DAX adds a new row to the related table that has blanks in every field, and links that new row to the unindexed row to guarantee referential integrity. If your function includes blank rows, such as is often the case when using ALL, those blank rows will then be included in the number of values returned for that column.

You can also create entire calculated tables using DAX functions. Calculated tables created using DAX require a NAME and a TABLE function. Calculated tables can be used like any other table, including establishing relationships.

Video content courtesy of Alberto Ferrari, SQLBI

Congratulations!

You've completed the Introduction to DAX section of Microsoft Power BI Guided learning.

You learned how to...

  • Davidiseminger
  • Adam Saxton
  • Theano Petersen