Originally released with Power Pivot in Excel, the initial function of Data Analysis Expression Language (DAX) was to help you solve complex business problems.
Now, not only is DAX used in Power Pivot Excel, but it is also the language used in Analysis Services Tabular, and Power BI. The functional language allows you to carry out both basic and complex calculations across multiple tables.
While Excel works on cell references and ranges of cells, DAX works on both row and filter context.
The term ‘functional language’ means that the language makes a call to a function known as an expression. The result from this expression can then be used in a call to another function.
Let's look at a simple DAX expression.
= DISTINCTCOUNT ('Sales'[customer_id])
Like Excel, DAX always starts with = and then calls an expression, which is a function or a formula. In this example, we have called the DISTINCTCOUNT function, which will count the distinct values of a selected column.
The table name is shown in the quotes ('Sales'). These quotes can be left out if the table name does not contain any special characters. After this, we reference the column in the table contained within the square brackets.
In this example, we have selected the customer ID column. This DAX expression will give us the number of unique customers that we have sold to.
DAX calculations
There are two types of DAX calculations, a calculated column and a measure. Both have their uses, and their benefits.
Something else worth noting is that DAX calculations are carried out in two contexts.
- a row context, which are the rows, or selected rows, of a table
- a filter context, which is defined by the filters on a pivot table, or visualisation
When it comes to writing DAX calculations, you’ll need to have a good understanding of the row and filter context. If you don’t take these into consideration, you may get an unexpected value or result. You will become particularly aware of this when working with data structure containing multiple tables and relationships.
Data Types
The following data types are supported by DAX:
Integer - a whole number
Decimal - a fraction of a number
Currency - a combination of an integer and decimal
Date and Time - a combination of an integer and decimal, with Date represented by a whole number, and Time represented by a decimal number
Boolean - a true or false result
String - text
Binary - an object such as a file or an image
Click here to view a video we've put together which should help explain things further.
You need to sign in or register before you can add a contribution.