Excel DAX - Functions

Advertisements

Previous Page
Next Page  

Most of the DAX functions have the same names and functionality as that of Excel functions. However, DAX functions have been modified to use DAX data types and to work with tables and columns.

DAX has some additional functions that you will not find in Excel. These DAX functions are provided for specific purposes such as lookups based on relationships associated with the relational database aspects of the Data Model, the ability to iterate over a table to perform recursive calculations, to perform dynamic aggregation, and for calculations utilizing time intelligence.

In this chapter, you will learn about the functions supported in the DAX language. For more information on the usage of these DAX functions, refer to the tutorial – DAX Functions in this tutorials library.

What is a DAX Function?

A DAX function is an in-built function provided in the DAX language to enable you to perform various actions on the data in the tables in your Data Model. As discussed earlier, DAX is used for data analysis and business intelligence purposes that require support to extract, assimilate, and derive insights from the data. The DAX functions that are based on the Data Model provide you with these utilities that make your job simpler, once you get a grasp on the DAX language and the usage of the DAX functions.

Excel Functions vs. DAX Functions

There are certain similarities between Excel functions that you are aware of and the DAX functions. However, there are certain differences too. You need to get a clarity on these, so that you can avoid making mistakes in the usage of DAX functions and in writing DAX formulas that include DAX functions.

Similarities between Excel Functions and DAX Functions

  • Many 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. In this tutorial, you will find every DAX function prefixed with DAX so as to avoid confusion with the Excel functions.

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

  • Excel functions take a cell reference or a range of cells as reference. DAX functions never take a cell reference or a range of cells as reference, but instead take a column or table as 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 ability 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 established between the tables.

  • Excel supports variant data type in a column of data, i.e. you can have data of different data types in a column. Whereas, DAX expects the data in a column of a table to be always of the same data type. If the data is not of the same data type, DAX changes the entire column to the data type that best accommodates all the values in the column. However, if the data is imported and this issue arises, DAX can flag an error.

To learn about DAX data types and data type casting, refer to the chapter – DAX Syntax Reference.

Types of DAX Functions

DAX supports the following types of functions.

  • DAX Table Valued Functions
    • DAX Filter Functions
    • DAX Aggregation Functions
    • DAX Time Intelligence 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

In this section, you will learn about DAX functions at the functions category level. For details on the DAX Function Syntax and what the DAX function returns and does - refer to the DAX Functions tutorial in this tutorials library.

DAX time intelligence functions and DAX filter functions are powerful and require a special mention. Refer to the chapters - Understanding DAX Time Intelligence and DAX Filter Functions for details.

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 intelligence functions

Understanding DAX table valued functions helps you in writing DAX formulas effectively.

DAX Aggregation Functions

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

Following are some DAX Aggregation functions −

  • ADDCOLUMNS (<table>, <name>, <expression>, [<name>, <expression>] …)

  • AVERAGE (<column>)

  • AVERAGEA (<column>)

  • AVERAGEX (<table>, <expression>)

  • COUNT (<column>)

  • COUNTA (<column>)

  • COUNTAX (<table>, <expression>)

  • COUNTBLANK (<column>)

  • COUNTROWS (<table>)

  • COUNTX (<table>, <expression>)

  • CROSSJOIN (<table1>, <table2>, [<table3>] …)

  • DISTINCTCOUNT (<column>)

  • GENERATE (<table1>, <table2>)

  • GENERATEALL (<table1>, <table2>)

  • MAX (<column>)

  • MAXA (<column>)

  • MAXX (<table>, <expression>)

  • MIN (<column>)

  • MINA (<column>)

  • MINX (<table>, <expression>)

  • PRODUCT (<column>)

  • PRODUCTX (<table>, <expression>)

  • ROW (<name>, <expression>, [<name>, <expression>] …)

  • SELECTCOLUMNS (<table>, <name>, <scalar_expression>,

  • [<name>, <scalar_expression>] …)

  • SUM (<column>)

  • SUMMARIZE (<table>, <groupBy_columnName>, [<groupBy_columnName>] …, [<name>, <expression>] …)

  • SUMX (<table>, <expression>)

  • TOPN (<n_value>, <table>, <orderBy_expression>, [<order>], [<orderBy_expression>, [<order>]] …)

DAX Filter Functions

DAX Filter functions return a column, 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.

Following are some DAX Filter functions −

  • ADDMISSINGITEMS(<showAllColumn>, [<showAllColumn>] …, <table>, <groupingColumn>, [<groupingColumn>] … [filterTable] …)

  • ALL( {<table> | <column>, [<column>], [<column>] …} )

  • ALLEXCEPT(<table>, <column>, [<column>] …)

  • ALLNOBLANKROW(<table>|<column>)

  • ALLSELECTED([<tableName> | <columnName>])

  • CALCULATE (<expression>, <filter1>, <filter2>…)

  • CALCULATETABLE (<expression>, <filter1>, <filter2>…)

  • CROSSFILTER (<columnName1>, <columnName2>, <direction>)

  • DISTINCT (<column>)

  • EARLIER(<column>, <number>)

  • EARLIEST(<column>)

  • FILTER(<table>,<filter>)

  • FILTERS(<columnName>)

  • HASONEFILTER(<columnName>)

  • HASONEVALUE(<columnName>)

  • ISCROSSFILTERED (<columnName>)

  • ISFILTERED (<columnName>)

  • KEEPFILTERS (<expression>)

  • RELATED(<column>)

  • RELATEDTABLE(<tableName>)

  • SUBSTITUTEWITHINDEX (<table>, <indexColumnName>, <indexColumnsTable>, <orderBy_expression>, [<order>], [<orderBy_expression>, [<order>]] …])

  • USERELATIONSHIP(<columnName1>,<columnName2>)

  • VALUES(<TableNameOrColumnName>)

DAX Time Intelligence Functions

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

Following are some DAX Time Intelligence functions −

  • CLOSINGBALANCEMONTH (<expression>,<dates>[,<filter>])

  • CLOSINGBALANCEQUARTER (<expression>,<dates>, [<filter>])

  • CLOSINGBALANCEYEAR (<expression>,<dates>, [<filter>], [<year_end_date>])

  • DATEADD (<dates>,<number_of_intervals>, <interval>)

  • DATESBETWEEN (<dates>,<start_date>,<end_date>)

  • DATESINPERIOD (<dates>,<start_date>, <number_of_intervals>,<interval>)

  • DATESMTD (<dates>)

  • DATESQTD (<dates>)

  • DATESYTD (<dates>, [<year_end_date>])

  • ENDOFMONTH (<dates>)

  • ENDOFQUARTER (<dates>)

  • ENDOFYEAR (<dates> , [<year_end_date>])

  • FIRSTDATE (<dates>)

  • FIRSTNONBLANK (<column>,<expression>)

  • LASTDATE (<dates>)

  • LASTNONBLANK (<column>,<expression>)

  • NEXTDAY (<dates>)

  • NEXTMONTH (<dates>)

  • NEXTQUARTER (<dates>)

  • NEXTYEAR (<dates>, [<year_end_date>])

  • OPENINGBALANCEMONTH (<expression>,<dates>, [<filter>])

  • OPENINGBALANCEQUARTER (<expression>,<dates>, [<filter>])

  • OPENINGBALANCEYEAR (<expression>,<dates>, [<filter>], [<year_end_date>])

  • PARALLELPERIOD (<dates>,<number_of_intervals>, <interval>)

  • PREVIOUSDAY(<dates>)

  • PREVIOUSMONTH(<dates>)

  • PREVIOUSQUARTER(<dates>)

  • PREVIOUSYEAR (<dates>, [<year_end_date>])

  • SAMEPERIODLASTYEAR(<dates>)

  • STARTOFMONTH(<dates>)

  • STARTOFQUARTER(<dates>)

  • STARTOFYEAR(<dates>)

  • TOTALMTD (<expression>,<dates>, [<filter>])

  • TOTALQTD(<expression>,<dates>, [<filter>])

  • TOTALYTD(<expression>,<dates>, [<filter>], [<year_end_date>])

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.

Following are DAX Date and Time functions −

  • DATE(<year>, <month>, <day>)
  • DATEVALUE(date_text)
  • DAY(<date>)
  • EDATE(<start_date>, <months>)
  • EOMONTH(<start_date>, <months>)
  • HOUR(<datetime>)
  • MINUTE(<datetime>)
  • MONTH(<datetime>)
  • NOW()
  • SECOND(<time>)
  • TIME(hour, minute, second)
  • TIMEVALUE(time_text)
  • TODAY()
  • WEEKDAY(<date>, <return_type>)
  • WEEKNUM(<date>, <return_type>)
  • YEAR(<date>)
  • YEARFRAC(<start_date>, <end_date>, <basis>)

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.

Following are some DAX Information functions −

  • CONTAINS (<table>, <columnName>, <value>, [<columnName>, <value>]…)

  • CUSTOMDATA()

  • ISBLANK(<value>)

  • ISERROR(<value>)

  • ISEVEN(number)

  • ISLOGICAL(<value>)

  • ISNONTEXT(<value>)

  • ISNUMBER(<value>)

  • ISONORAFTER (<scalar_expression>, <scalar_expression>, [sort_order], [<scalar_expression>, <scalar_expression>, [sort_order]]…)

  • ISTEXT(<value>)

  • LOOKUPVALUE (<result_columnName>, <search_columnName>, <search_value>, [<search_columnName>, <search_value>]…)

  • USERNAME()

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.

Following are DAX Logical functions −

  • AND(<logical1>,<logical2>)
  • FALSE()
  • IF(logical_test>,<value_if_true>, value_if_false)
  • IFERROR(value, value_if_error)
  • NOT(<logical>)
  • OR(<logical1>,<logical2>)
  • SWITCH(<expression>, <value>, <result>, [<value>, <result>]…, [<else>])
  • TRUE()

DAX Math and Trig Functions

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

Following are some DAX Math and Trig functions −

  • ABS(<number>)
  • ACOS(number)
  • ACOSH(number)
  • ASIN(number)
  • ASINH(number)
  • ATAN(number)
  • ATANH(number)
  • CEILING(<number>, <significance>)
  • COMBIN(number, number_chosen)
  • COMBINA(number, number_chosen)
  • COS(number)
  • COSH(number)
  • CURRENCY(<value>)
  • DEGREES(angle)
  • DIVIDE(<numerator>, <denominator>, [<alternateresult>])
  • EVEN(number)
  • EXP(<number>)
  • FACT(<number>)
  • FLOOR(<number>, <significance>)
  • GCD(number1, [number2], ...)
  • INT(<number>)
  • ISO.CEILING(<number>, [<significance>])
  • LCM(number1, [number2], ...)
  • LN(<number>)
  • LOG(<number>,<base>)
  • LOG10(<number>)
  • INT(<number>)
  • MROUND(<number>, <multiple>)
  • ODD(number)
  • PI()
  • POWER(<number>, <power>)
  • PRODUCT(<column>)
  • PRODUCTX(<table>, <expression>)
  • QUOTIENT(<numerator>, <denominator>)
  • RADIANS(angle)
  • RAND()
  • RANDBETWEEN(<bottom>,<top>)
  • ROUND(<number>, <num_digits>)
  • ROUNDDOWN(<number>, <num_digits>)
  • ROUNDUP(<number>, <num_digits>)
  • SIN(number)
  • SINH(number)
  • SIGN(<number>)
  • SQRT(<number>)
  • SUM(<column>)
  • SUMX(<table>, <expression>)
  • TAN(number)
  • TANH(number)
  • TRUNC(<number>,<num_digits>)

DAX Other Functions

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

Following are some DAX Other functions −

  • EXCEPT(<table_expression1>, <table_expression2>

  • GROUPBY (<table>, [<groupBy_columnName1>], [<name>, <expression>] … )

  • INTERSECT(<table_expression1>, <table_expression2>)

  • ISEMPTY(<table_expression>)

  • NATURALINNERJOIN(<leftJoinTable>, <rightJoinTable>)

  • NATURALLEFTOUTERJOIN(<leftJoinTable>, <rightJoinTable>)

  • SUMMARIZECOLUMNS (<groupBy_columnName>, [<groupBy_columnName>]…, [<filterTable>] …, [<name>, <expression>]…)

  • UNION (<table_expression1>, <table_expression2>, [<table_expression>]…)

  • VAR <name> = <expression>

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.

Following are some DAX Parent and Child functions −

  • PATH(<ID_columnName>, <parent_columnName>)
  • PATHCONTAINS(<path>, <item>)
  • PATHITEM(<path>, <position>, [<type>])
  • PATHITEMREVERSE(<path>, <position>, [<type>])
  • PATHLENGTH(<path>)

DAX Statistical Functions

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

Following are some DAX Statistical functions −

  • BETA.DIST(x, alpha, beta, cumulative,[A],[B])

  • BETA.INV(probability, alpha, beta,[A],[B])

  • CHISQ.INV(probability, deg_freedom)

  • CHISQ.INV.RT(probability, deg_freedom)

  • CONFIDENCE.NORM(alpha, standard_dev, size)

  • CONFIDENCE.T(alpha, standard_dev, size)

  • DATATABLE (ColumnName1, DataType1, ColumnName2, DataType2 ..., {{Value1, Value2...}, {ValueN, ValueN+1...}...})

  • EXPON.DIST(x, lambda, cumulative)

  • GEOMEAN(<column>)

  • GEOMEANX(<table>, <expression>)

  • MEDIAN(<column>)

  • MEDIANX(<table>, <expression>)

  • PERCENTILE.EXC(<column>, <k>)

  • PERCENTILE.INC(<column>, <k>)

  • PERCENTILEX.EXC(<table>, <expression>, k)

  • PERCENTILEX.EXC(<table>, <expression>, k)

  • POISSON.DIST(x, mean, cumulative)

  • RANK.EQ(<value>, <columnName>[, <order>])

  • RANKX(<table>, <expression>[, <value>[, <order>[, <ties>]]])

  • SAMPLE (<n_value>, <table>, <orderBy_expression>, [<order>], [<orderBy_expression>, [<order>]]…)

  • STDEV.P(<ColumnName>)

  • STDEV.S(<ColumnName>)

  • STDEVX.P(<table>, <expression>)

  • STDEVX.S(<table>, <expression>)

  • SQRTPI(number)

  • VAR.P(<columnName>)

  • VAR.S(<columnName>)

  • VARX.P(<table>, <expression>)

  • VARX.S(<table>, <expression>)

  • XIRR(<table>, <values>, <dates>, [guess])

  • XNPV(<table>, <values>, <dates>, <rate>)

DAX Text Functions

DAX Text functions work with tables and columns. With DAX Text functions you can return the 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.

Following are some DAX Text functions −

  • BLANK()
  • CODE(text)
  • CONCATENATE(<text1>, <text2>)
  • CONCATENATEX(<table>, <expression>, [delimiter])
  • EXACT(<text1>,<text2>)
  • FIND(<find_text>, <within_text>, [<start_num>], [<NotFoundValue>])
  • FIXED(<number>, <decimals>, <no_commas>)
  • FORMAT(<value>, <format_string>)
  • LEFT(<text>, <num_chars>)
  • LEN(<text>)
  • LOWER(<text>)
  • MID(<text>, <start_num>, <num_chars>)
  • REPLACE(<old_text>, <start_num>, <num_chars>, <new_text>)
  • REPT(<text>, <num_times>)
  • RIGHT(<text>, <num_chars>)
  • SEARCH(<find_text>, <within_text>, [<start_num>], [<NotFoundValue>])
  • SUBSTITUTE(<text>, <old_text>, <new_text>, <instance_num>)
  • TRIM(<text>)
  • UPPER (<text>)
  • VALUE(<text>)
Advertisements