DAX Functions - Introduction

DAX stands for Data Analysis Expressions. DAX is a formula language and is a collection of functions, operators, and constants that can be used in a formula or expression to calculate and return one or more values. DAX is the formula language associated with the Data Model of Microsoft Excel Power Pivot and with Microsoft Power BI.

DAX is not a programming language, however it is a formula language that allows the users to define custom calculations in calculated columns and calculated fields (also known as measures). DAX helps you create new information from the existing data in your Data Model. DAX formulas enable you to perform data modepng, data analysis, and use the results for reporting and decision making.

For an in-depth understanding of DAX, refer to the tutorial – DAX in this tutorials pbrary.

What is a DAX Function?

A DAX function is an inbuilt function provided in the DAX language to enable you to perform various actions on the data in the tables in your Data Model.

DAX functions enable you to perform commonly used data calculations on the Data Model. Some of the DAX functions have same names and functionapty as that of Excel functions but have been modified to use DAX data types and to work with tables and columns, as highpghted in the next section. DAX has additional functions that are designed to work with relational data and perform dynamic aggregation.

DAX functions play an important role in the usage of DAX for data modepng and reporting.

Excel Functions vs. DAX Functions

There are certain similarities between the Excel functions and the DAX functions and there are certain differences too. Following are the similarities and differences between Excel functions and DAX functions −

Similarities Between Excel Functions and DAX Functions

Certain DAX functions have the same name and the same general behavior as Excel functions.

DAX has lookup functions that are similar to the array and vector lookup functions in Excel.

Differences Between Excel Functions and DAX Functions

DAX functions have been modified to take different types of inputs and some of the DAX functions might return a different data type. Hence, you need to understand the usage of these functions separately though they have the same name.

You cannot use DAX functions in an Excel formula or use Excel functions in DAX formula, without the required modifications.

Excel functions take a cell reference or a range of cells as a reference. DAX functions never take a cell reference or a range of cells as a reference, but instead take a column or table as a reference.

Excel date and time functions return an integer that represents a date as a serial number. DAX date and time functions return a datetime data type that is in DAX but not in Excel.

Excel has no functions that return a table, but some functions can work with arrays. Many of the DAX functions can easily reference complete tables and columns to perform calculations and return a table or a column of values. This abipty of DAX adds power to the Power Pivot, Power View and Power BI, where DAX is used.

DAX lookup functions require that a relationship is estabpshed between the respective tables.

DAX Parameter Naming Conventions

DAX has standard parameter names to faciptate the usage and understanding of the DAX functions. Further, you can use certain prefixes to the parameter names. If the prefix is clear enough, you can use the prefix itself as the parameter name.

You need to understand DAX parameter naming conventions so as to understand the syntax of the DAX functions and use the values for the required parameters correctly.

Refer to the chapter − DAX Parameter Naming Conventions for details.

Types of DAX Functions

DAX supports the following types of functions.

DAX Table-Valued Functions

DAX Filter Functions

DAX Aggregation Functions

DAX Time Intelpgence Functions

DAX Date and Time Functions

DAX Information Functions

DAX Logical Functions

DAX Math and Trig Functions

DAX Other Functions

DAX Parent and Child Functions

DAX Statistical Functions

DAX Text Functions

DAX Description Structure Functions

DAX Table-Valued Functions

Many DAX functions take tables as input or output tables or do both. These DAX functions are called DAX table-valued functions. Because a table can have a single column, DAX table-valued functions also take single columns as inputs. You have the following types of DAX table-valued functions −

DAX Aggregation functions

DAX Filter functions

DAX Time intelpgence functions

DAX Aggregation Functions

DAX Aggregation functions aggregate any expression over the rows of a table and are useful in calculations.

Refer to the chapter − DAX Aggregation functions for details.

DAX Filter Functions

DAX Filter functions return a column or a table or values related to the current row. You can use DAX Filter functions to return specific data types, look up values in related tables and filter by related values. DAX Lookup functions work by using tables and relationships between them. DAX Filter functions enable you to manipulate the data context to create dynamic calculations.

Refer to the chapter − DAX Filter functions for details.

DAX Time Intelpgence Functions

DAX Time Intelpgence functions return a table of dates or the use a table of dates to calculate an aggregation. These DAX functions help you create calculations that support the needs of Business Intelpgence analysis by enabpng you to manipulate data using time periods, including days, months, quarters, and years.

Refer to the chapter − DAX Time Intelpgence functions for details.

DAX Date and Time Functions

DAX Date and Time functions are similar to the Excel date and time functions. However, DAX Date and Time functions are based on the datetime data type of DAX.

Refer to the chapter − DAX Date and Time functions for details.

DAX Information Functions

DAX Information functions look at the cell or row that is provided as an argument and tell you whether the value matches the expected type.

Refer to the chapter − DAX Information functions for details.

DAX Logical Functions

DAX Logical Functions return information about values in an expression. For example, DAX TRUE function lets you know whether an expression that you are evaluating returns a TRUE value.

Refer to the chapter − DAX Logical functions for details.

DAX Math and Trig Functions

DAX Mathematical and Trigonometric functions are very similar to the Excel mathematical and trigonometric functions.

Refer to the chapter − DAX Math and Trig functions for details.

DAX Parent and Child Functions

DAX Parent and Child functions are useful in managing data that is presented as a parent/child hierarchy in the Data Model.

Refer to the chapter − DAX Parent and Child functions for details.

DAX Statistical Functions

DAX Statistical functions are very similar to the Excel Statistical functions.

Refer to the chapter − DAX Statistical functions for details.

DAX Text Functions

DAX Text functions work with tables and columns. With DAX Text functions, you can return part of a string, search for text within a string or concatenate string values. You can also control the formats for dates, times, and numbers.

Refer to the chapter − DAX Text functions for details.

DAX Other Functions

These DAX functions perform unique actions that cannot be defined by any of the categories most other functions belong to.

Refer to the chapter − DAX Other functions for details.

DAX Function Description Structure

If you have to use a DAX function in a DAX formula, you need to understand the function in detail. You should know the syntax of the function, the parameter types, what the function returns, etc.

In this tutorial, a common function description structure is used for all the DAX functions so that you can read and interpret the DAX functions effectively.

Refer to the chapter − DAX Function Description Structure for details.