Overview of Functions in DAX
DAX functions are covered in every Power BI training in Sydney and Melbourne, DAX is used to bring about some meaningful information hidden inside raw data. This is done using functions in DAX. Functions in DAX perform data manipulation. A function in DAX is a named formula within an expression.
Let’s see what a simple DAX expression looks like:`
- This is the name of the expression that you are going to create.
- Equal sign represents the beginning of DAX expression.
- Formula represents the calculation that you want to perform.
- Name of Table.
- Name of Column.
This DAX expression is typed in a formula bar:
The arguments added into the function as input are known as parameters. Parameters can be both optional as well as required. DAX functions can be used to:
- Perform calculations on date/time columns.
- Iterate over a table.
- Work with strings.
- Create conditional values.
- Perform lookups.
Categories of functions based on their execution in DAX
There are two broad categories of functions based on their execution in DAX are:
Regular Functions
Regular functions simply calculate the values based on the filter selection. They are used to perform calculations for a column.
Examples of Regular Functions:
- Sum
- Count
- Average
- Min
- Max
Iterator Functions
Iterator functions are used to perform calculations for expressions. The X at the end of the functions signifies their use.
The iterator functions go through every single record of data and calculate the result of expression for that record. It then stores the results in a temporary memory. After the function has parsed the complete table, this temporary memory is released and the aggregated results are shown.
Examples of Iterator Functions:
- SumX
- CountX
- AverageX
- MinX
- MaxX
- FilterX
Formulas in DAX look similar to excel. They are however technically different.
Difference between EXCEL and DAX Formulas:
- DAX functions refer to a complete column and not a value.
- To refer to a particular value in DAX, filters need to be specified.
- If you want to do row-by-row evaluation, a row context needs to be created.
- DAX includes many functions which can return tables and they are used as inputs for other functions.
- DAX functions include a variety a time-intelligence functions.
Logical distribution of DAX functions:
DAX functions can be logically categorized as:
Date and time functions:
These functions are used for date and time calculations and are similar to excel. Some functions from this category are:
Source: Date and Time Function in Dax
Filter functions:
Filter functions allow you to manipulate data context and create dynamic calculations. These functions in DAX return specific values based on the tables and relationships. Some common filter functions include:
Source: Filter Function in Dax
Financial functions:
These functions are used to perform financial calculations such as net present value and rate of return. These are similar to excel. Some functions from this category are
Source: Financial Functions in Dax
Information functions:
Information functions evaluate a value and tell whether it matches the function argument or not. An example of this function is ISERROR functions. Some functions from this category are:
Source: Information Function in Dax
Logical functions:
Logical functions and evaluate an expression and return value about the expression such as TRUE and FALSE. Some functions from this category are:
Source: Logical Functions in Dax
Text functions:
Text functions in DAX are similar to their counterparts in EXCEL. Text functions are used to perform text based operations such as concatenate a string, find a part of string, etc. Some functions from this category are:
Source: Text Function in Dax
Mathematical and trigonometrical functions:
Mathematical and trigonometric functions are used for mathematical and trigonometric calculations. They are similar to their counterparts in DAX. Some functions from this category are:
Source: Time Intelligence Function
Statistical functions:
Statistical functions in DAX are used for statistical calculations. They are also similar to their EXCEL counterparts. Some functions from this category are:
Source: Statistical Function Dax
Table-valued functions:
Table-valued functions take tables as input, outputs or as both. DAX include the following table-valued functions:
- Filter functions.
- Aggregator Functions.
- Time intelligence functions.
Time intelligence functions:
Time intelligence functions in DAX are used to perform time based analysis. They are used in combination with other functions to discover meaningful insights. Some functions from this category are:
Source: Time Intelligence Function Dax
Conclusion:
DAX is a formula language which means that every code written in DAX is in the form of formulas. Understanding the formulas is important to use them. DAX formulas look similar to excel however, technically, they perform differently. This blog post provides an overview of functions in DAX.
Are you looking for an instructor-led Power BI or Power Apps training in Australia? We provide classroom as well as online live trainings for the following courses: