Types of DAX Functions
DAX provides functions that have the same functionality and names as the Excel functions that you are already familiar with. However, the functions have been modified to use DAX data types and to work with tables and columns. In addition, DAX provides many specialized functions for specific purposes, such as lookups based on relationships, the ability to iterate over a table to perform recursive calculations, and calculations utilizing time intelligence.
This section provides an overview of the types of functions supported in the DAX language.
Table-Valued Functions
There are many new DAX functions that output tables, take tables as input, or do both. Because a table can have a single column, table-valued functions also take single columns as inputs. DAX includes the following types of table-valued functions:
Filter functions Return a column, table, or values related to the current row.
Aggregation functions Aggregate any expression over the rows of a table.
Time intelligence functions Return a table of dates, or use a table of dates to calculate an aggregation.
Understanding how to use these table-valued functions is important for fully utilizing DAX formulas. For more information, see DAX Function Reference for PowerPivot.
Back to Top
Date and Time Functions
The date and time functions in DAX are similar to date and time functions in Microsoft Excel. However, DAX functions are based on the datetime data types used by Microsoft SQL Server. For more information, see Date and Time Functions (DAX).
Filter Functions
The filter functions in DAX let you return specific data types, look up values in related tales, and filter by related values. The lookup functions work by using tables and relationships, like a database. The filtering functions let you manipulate data context to create dynamic calculations. For more information, see Filter Functions (DAX).
Information Functions
An information function looks at the cell or row that is provided as an argument and tells you whether the value matches the expected type. For example, the ISERROR function returns TRUE if the value that you reference contains an error. For more information, see Information Functions (DAX).
Logical Functions
Logical functions act upon an expression to return information about the values in the expression. For example, the TRUE function lets you know whether an expression that you are evaluating returns a TRUE value. For more information, see Logical Functions (DAX).
Mathematical and Trigonometric Functions
The mathematical functions in DAX are very similar to the Excel mathematical and trigonometric functions. Some minor differences exist in the numeric data types used by DAX functions. For more information, see Math and Trigonometric Functions (DAX).
Statistical Functions
PowerPivot provides statistical functions that perform aggregations, similar to those in Microsoft Excel. In addition to creating sums and averages, or finding the minimum and maximum values, in DAX you can also filter a column before aggregating or create aggregations based on related tables. For more information, see Statistical Functions (DAX).
Text Functions
The text functions in DAX are very similar to their counterparts in Excel. You can return part of a string, search for text within a string, or concatenate string values. DAX also provides functions for controlling the formats for dates, times, and numbers. For more information, see Text Functions (DAX).
Time Intelligence Functions
The time intelligence functions provided in DAX let you create calculations that use built-in knowledge about calendars and dates. By using time and date ranges in combination with aggregations or calculations, you can build meaningful comparisons across comparable time periods for sales, inventory, and so on. For more information, see Time Intelligence Functions (DAX).
See Also