DAX Functions

The Data Analysis Expressions (DAX) helps to work with ease with data models using formulas and expressions. DAX is used in many Microsoft products like Power BI and Power Pivot for Excel. DAX formulas helps in advanced calculations and queries on data in tabular data.

DAX formulas are essential for creating calculations in calculated columns and measures and securing your data by using row level filters.

DAX includes functions that can be used to perform calculations using dates and times, create conditional values, work with strings, perform lookups based on relationships, and perform recursive calculations.

Though they are like Excel functions, DAX formulas are different in some important ways that include:

A DAX function always references a complete column, or a table and we need to add filters if we want to use only particular values from a table or column.

DAX includes many functions that return a table, rather than a value. This table is used to provide input to other functions.

DAX functions include a variety of time-intelligence functions. For example, we can compare sums across parallel periods.

The various DAX functions can be grouped under the following categories.

Text Functions, Statistical Functions, Parent & Child Functions, Math & Trig Functions, Logical Functions, Information Functions, Filter Functions, Date And Time Functions and Time And Intelligence Functions

Our sections on DAX functions provides a basic introduction to the important functions in DAX.

Contents

1 Date & Time Function
2 Filter Function
3 Information Function
4 Math & TrigFunction
5 Statistical Function
6 Text Function
7 Logical Function
8 Parent & Child Function

EDATE	Returns a date	start_date months
EOMONTH	Returns a date	start_date months
HOUR	Returns integer numbers representing hours	datetime
MINUTE	Returns integer numbers representing minutes	datetime
TODAY	Returns date
UTCNOW	Returns date and time
UTCTODAY	Returns date
WEEKDAY	Returns integer numbers of a day of a week	date return_type
WEEKNUM	Returns integer numbers of week from year	date return_type
YEAR	Returns integer numbers ranging between 1900-9999	date
CLOSINGBALANCEMONTH	Returns a scalar value	expression dates filter
CLOSINGBALANCEYEAR	Returns a scalar value	expression dates filter year_end_date
CLOSINGBALANCEQUARTER	Returns a scalar value	expression dates filter
DATEADD	Returns date values	dates number_of_intervals interval
DATESBETWEEN	Returns date values	dates start_date end_date
LASTDATE	Returns date values	dates
LASTNONBLANK	Returns the last value for the specified column	column expression
LASTNONBLANKVALUE	Returns the last value for the specified column	column expression
NEXTDAY	Returns day	dates
NEXTMONTH	Returns month	dates
NEXTQUARTER	Returns date	dates
NEXTYEAR	Returns a year	dates year_end_date
FIRSTDATE	Returns the first date in the current context for the specified column of dates	dates
STARTOFQUARTER	Returns the first date of the quarter in the current context for the specified column of dates	dates
STARTOFMONTH	Returns the first date of the month in the current context for the specified column of dates	dates
SAMEPERIODLASTYEAR	Returns a table that contains a column of dates shifted one year back in time from the dates in the specified dates column	dates
PREVIOUSYEAR	Returns a table that contains a column of all dates from the previous year, given the last date in the dates column	dates year_end_date
PREVIOUSQUARTER	Returns a table that contains a column of all dates from the previous quarter	dates
PREVIOUSMONTH	Returns a table that contains a column of all dates from the previous month, based on the first date in the dates column, in the current context	dates
PREVIOUSDAY	Returns a table that contains a column of all dates representing the day that is previous to the first date in the dates column, in the current context	dates
PARALLELPERIOD	Returns a table that contains a column of dates	dates number_of_intervals inteval
FIRSTNONBLANKVALUE	Returns the value evaluated by the Expression iterating ColumnName	column expression
ENDOFYEAR	Returns the last date of the year in the current context for the specified column of dates	dates year_end_date
ENDOFQUARTER	Returns the last date of the quarter in the current context for the specified column of dates	dates
ENDOFMONTH	Returns the last date of the month in the current context for the specified column of dates	dates
DAY	Returns integer numbers of a day of a month	dates
DATE	Returns the specified date in datetime format	year month day
DATEDIFF	Returns the count of interval boundaries crossed between two dates	start_date end_date interval
DATESInPeriod	Returns A table containing a single column of date values	dates start_date number_of_intervals interval
DATESMTD	Returns a table containing a single column of date values	dates
DATESQTD	Returns a table containing a single column of date values	dates
DATESYTD	Returns a table containing a single column of date values	dates year_end_date
DATEVALUE	Returns a date in datetime format	date_text
FIRSTNONBLANK	Returns the first value in the column, column, filtered by the current context, where the expression is not blank	column expression
MONTH	Returns an integer number indicating the number of the month	datetime
NOW	Returns the current date and time in datetime format
OPENINGBALANCEMONTH	Evaluate the expression on the first date of the month	expression dates filter
OPENINGBALANCEQUARTER	Returns the expression representing firtsr date of the quarter.	expression dates filter
OPENINGBALANCEYEAR	Returns scalar value that represents the expression evaluated at the first date of the quarter in the current context	expression dates filter year_end_date
SECOND	Returns seconds of the time value,0o 59	time
STARTOFYEAR	Returns the first date of the year in the current context for the specified column of dates	dates
TIME	Returns the specified time in DateTime format	hour minute second
TIMEVALUE	Returns a date	time_text
TOTALMTD	Returns the value of the expression for the month to date in the current context	expression dates filter
TOTALQTD	Returns a scalar value representing the expression calculated for the dates in the current quarter to date	expression dates filter
TOTALYTD	Returns a scalar value representing the expression calculated for the current year to date	expression dates filter
YEARFRAC	Calculates the fraction of the year represented by the number of whole days between two dates	start_date end_date basis

ALL	Is useful for clearing filters and creating calculations on all the rows in a table	table column column …
CURRENTGROUP	Returns the set of table of GROUPBY that belong to the current row of the GROUPBY result
DATATABLE	Returns a table with data defined inline	name type name type data
DETAILROWS	Returns a table obtained by evaluating the Detail Rows Expression of the measure passed as an argument	measure
DISTINCT	Returns a column of unique value	column
EARLIER	Returns the value of row, from column, at number of outer evaluation passes	ColumnName number
EARLIEST	Returns the current value of row, from column, at the outermost row context	Column
EXCEPT	Returns a table that contains the rows of the LeftTable minus all the rows of the RightTable	table_expression1 table_expression2
FILTER	Returns a table with filtered row	table filter
FILTERS	Returns a column of unique values	ColumnName
GENERATE	Returns a crossjoin of first table	table1 table2
GENERATEALL	Returns an entire table or a table with one or more columns	table1 table2
GROUPBY	Returns a table with the Cartesian product between each row in table1 and the table that results from evaluating table2 in the context of the current row from table1.	table ColumnName1 name expression
IGNORE	Does not return any value	measure_expression
INTERSECT	Returns the row intersection of two tables	table_expression1 table_expression2
ISONORAFTER	Returns TRUE if any of the comparisons is TRUE, otherwise returns FALSE	value1 value2 order …
ADDCOLUMNS	Used to add a calculated column in a table	table name expression …
ADDMISSINGITEMS	Used to add missing items from other columns	ShowAll_ColumnName TableName GroupBy_ColumnName FilterTable …
ALLEXCEPT	Used to remove context filters in the table	table column …
ALLNOBLANKROW	Used to return rows that are not blank	table column columns …
ALLSELECTED	Used to display the rows and columns of the table	TableName ColumnName ColumnName …
CALCULATE	Used to calculate an expression in the current context	expression filter1 filter2
CALCULATEABLE	Used to classify a table expression in the current context	expression filter1 filter2
CALENDAR	Used to add a date column	start_date end_date
CALENDARAUTO	Used to add a date column based on the data model	fiscal_year_end_month
CROSSFILTER	Used to specify the cross-filter direction if a relationship exists between two columns	ColumnName1 ColumnName2 direction
CROSSJOIN	Used to crossjoin the rows and columns and bring out similar values	table table …
KEEPFILTERS	Modifies how filters are applied while evaluating a CALCULATE or CALCULATETABLE function	expression
LOOKUPVALUE	Retrieves a value from a table	result_ColumnName Search_ColumnName Search_value …
NATURALINNERJOIN	Performs an inner join of a table with another table	LeftJoinTable RightJoinTable
NATURALLEFTOUTERJOIN	Performs an outer join of a table with another table	LeftJoinTable RightJoinTable
RELATED	Returns a related value from another table	ColumnName
RELATEDTABLE	Returns a table of values	TableName
ROLLUP	Used as an argument inside the Summarize function	GroupBy_ColumnName GroupBy_ColumnName …
ROLLUPADDISSUBTOTAL	Used exclusively within SUMMARIZECOLUMNS	GrandtotalFilter GroupBy_ColumnName GroupLevelFilter …
ROLLUPGROUP	Used exclusively within SUMMARIZE or ADDMISSINGITEMS	GroupBy_ColumnName GroupBy_ColumnName …
ROLLUPISSUBTOTAL	Returns only marks a subset of columns to ADDMISSINGITEMS	GrandTotalFilter GroupBy_ColumnName sSubtotal_ColumnName GroupLevelFilter …
ROW	Returns a single row table with new columns	name expression …
SAMPLE	Returns a sample of N rows from the specified table	n_value table OrderBy_expression order …
SUBSTITUTEWITHINDEX	Returns a table which represents a left semijoin of the two tables supplied as arguments	table IndexColumnName IndexColumnsTable OrderBy_expression order …
SUMMARIZE	Returns a summary table for the requested totals over a set of groups	table GroupBy_ColumnName name expression …
SUMMARIZECOLUMNS	Returns a summary table over a set of groups	GroupBy_ColumnName FilterTable name expression
TOPN	Returns to get Top value from the specified table user	n_value table OrderBy_expression Order
UNION	U to join the table from a pair of tables	table_expression1 table_expression2 table_expression …
USERELATIONSHIP	Used to get specifies the relationship to be used in a specific calculation that has existed between columnName1 and columnName2	ColumnName1 ColumnName2
VALUES	Used in this function is the column name, a single-column table	TableName
SELECTCOLUMNS	Returns the calculated columns in the table	table name scalar_expression …

ISBLANK	Returns out the blank cells	value
ISCROSSFILTERED	Used to filter the same column	ColumnName
ISEMPTY	Used to check whether the table has any blank	table_expression
ISERROR	Used to check whether the value has any error	value
ISEVEN	Used to check whether the value is an even number	number
ISFILTERED	Used to find the weather column are filters directly	ColumnName
ISLOGICAL	Used to check whether the given logical value is Ture or False	value
ISNONTEXT	Used to check whether the given value is text or not	value
ISNUMBER	Used to check whether the given value is number or not	value
ISODD	Used to check whether the given value is ODD or not	number
ISSUBTOTAL	Checks whether the find the subtotal in columns	ColumnName
ISTEXT	Used to find whether the value is text or not	value
USERNAME	Used to find the domain name and the user name
USERPRINCIPALNAME	Used to find the user name from the mail id
CONTAINS	Returns a specified value that can be found in the corresponding columnNam	table Column_Name value …
CONTAINSROW	Returns the row of values that exists in the table	Table_expression Scalar_expression …
CUSTOMDATA	Used maintenance the CustomData property in the connection string
HASONEFILTER	Works with a direct filter down the values on ColumnName	ColumnName
HASONEVALUE	Used to filter down the distinct values in the columns	ColumnName

ABS	Returns a decimal value	number
ACOS	Returns the angle in radians between 0 and p	number
ACOSH	Returns an inverse hyperbolic cosine	number
ASIN	Returns an inverse hyperbolic cosine	number
ASINH	Returns the hyperbolic sine of a number	number
ATAN	Returns an arctangent, or inverse tangent, of a number	number
ATANH	Returns an inverse hyperbolic tangent value	number
CEILING	Returns an integer value	number significance
COMBIN	Returns a number of combinations for a given number of items	number number_chosen
COMBINA	Returns a number of combinations for a given number of items	number number_chosen
INT	Returns an integer value	number
COS	Returns the cosine given angle	number
COSH	Returns the hyperbolic cosine of a number	number
CURRENCY	Returns the value of the expression evaluated	value
DEGREES	Returns the value of the expression in degrees	angle
DIVIDE	Returns the result of the division	numerator denominator alternateresult
EVEN	Returns a number rounded up to the nearest even integer	number
EXP	Returns e raised to the power of a given number	number
SUM	Returns the result of sum	column
SUMX	Returns the sum of an expression evaluated for each row in a table	table expression
FLOOR	Returns the number down to requested significance	number significance
GCD	Returns the greatest common divisor of two or more integers	number1 number2
ISO.CEILING	Returns a number, of the same type as the number argument, rounded as specified	number significance
LCM	Returns the least common multiple of integers	number1 number2
LN	Returns the natural logarithm of the number passed as an argument	number
LOG	Returns the logarithm of number to the base specified	number base
LOG10	Returns the logarithm of number to base 10	number
MOD	Returns an integer number	number division
MROUND	Returns a rounded decimal number	number multiple
ODD	Returns number rounded up to the nearest odd integer	number
FACT	Returns the factorial of a number, equal to the series 123… , ending in the given number	number
PI	Returns the value of p, 3.14159265358979, accurate to 15 digits
POWER	Returns a decimal number	number power
PRODUCT	Returns the product of the numbers in a column	column
PRODUCTX	Returns the product of the Expression evaluated for each row in the Table	table expression
QUOTIENT	Returns a whole number	numerator denominator
ACOTH	Returns the inverse hyperbolic cotangent of a number	number
ACOT	Returns the principal value of the arccotangent, or inverse cotangent, of a number	number
BETA.DIST	Returns the beta distribution	X alpha beta cumulative A B
BETA.INV	Returns the inverse of the beta cumulative probability density function (BETA.DIST)	probability alpha beta A B
CHISQ.DIST	Returns the chi-squared distribution	X deg_freedom cumulative
CHISQ.DIST.RT	Returns the right-tailed probability of the chi-squared distribution	X deg_freedom
CHISQ.INV	Returns the inverse of the left-tailed probability of the chi-squared distribution	probability deg_freedom
CHISQ.INV.RT	Returns the inverse of the right-tailed probability of the chi-squared distribution	probability deg_freedom
CONFIDENCE.NORM	Returns the confidence interval for a population mean, using a normal distribution	alpha standard_dev size
CONFIDENCE.T	This function uses students t distribution and returns confidence interval	alpha standard_dev size
COT	Returns the cotangent of an angle specified in radians	number
COTH	Returns the hyperbolic cotangent of a hyperbolic angle	number
EXPON.DIST	Returns exponential distribution	X lambda cumulative
PERMUT	Returns the number of permutations for a given number of objects that can be selected from a number of objects	number number_choosen
POISSON.DIST	Returns the Poisson distribution	X mean cumulative
RADIANS	Returns the radians	angle
RAND	Returns a random number greater than or equal to 0 and less than 1, evenly distributed
RANDBETWEEN	Returns a random number in the range between two numbers you specify	bottom top
ROUND	Is used to round a number to a specific digit	number num_digits
ROUNDDOWN	Returns a decimal value	number num_digits
ROUNDUP	Is used to round a number	number num_digits
SIGN	Returns 1, 0 or -1, according to computation	number
SIN	Returns sine of an angle	number
SINH	Returns hyperbolic sine of an angle	number
SQRT	Returns the square root	number
SQRTPI	Returns the square root for number*pi	number
TAN	Returns the tangent of a given angle	number
TANH	Returns the hyperbolic tangent of a given angle	number
TRUNC	Returns to convert a number to an integer	number num_digits

GEOMEAN	Returns geometric mean of given column reference	column
AVERAGE	Returns to compute the arithmetic mean	column
AVERAGEA	Returns to compute the arithmetic mean	column
AVERAGEX	Returns to compute the arithmetic mean	table expression
GEOMEANX	Returns the geometric mean of an expression evaluated for each row in a table	table expression
MAX	Returns the largest value in a column, or between two scalar expressions	expression1
COUNT	Returns to count the number of cells in a columN	column
COUNTA	Returns to count the number of cells in a column	column
MAXA	Returns the largest value in a column, or between two scalar expressions	column
MAXX	Returns the largest value	table expression
MEDIAN	Returns the median of numbers in a column	column
MEDIANX	Returns the median number of an expression evaluated for each row in a table	table expression
MIN	Returns the smallest value	column expression1 expression2
COUNTAX	Returns to count the number of cells in a table	table expression
COUNTBLANK	Returns to count blank cells	column
MINA	Returns the smallest value	column
MINX	Returns the smallest value	table expression
PERCENTILE.EXC	Returns the k-th (exclusive) percentile of values in a column	column K
COUNTROWS	Returns to count a number of rows	table
COUNTX	Returns to count a number of rows that are not blank	table expression
DISTINCTCOUNT	Returns to count distinct values	column
PERCENTILE.INC	Returns the kth percentile value	column K
PERCENTILEX.EXC	Returns the kth percentile value of expression evaluated in a table	table expression
PERCENTILEX.INC	Returns the percentile value of expression evaluated	table expression K
RANK.EQ	Returns to rank a number from a list of numbers	value ColumnName order
RANKX	Returns to rank a number from a list of numbers through an expression	table expression value order ties
STDEV.P	Returns to calculate standard deviation based on population	ColumnName
STDEV.S	Returns to calculate the standard deviation based on the sample population	ColumnName
STDEVX.S	Returns the standard deviation of the sample population by evaluating the expression to the each row of the table	table expression
STDEVX.P	Returns the standard deviation of the entire population by evaluating the expression to each row of the table	table expression
VAR.P	Returns variance based on population	ColumnName
VAR.S	Returns the variance based on the sample population	ColumnName
VARX.P	Returns the variance of the entire population by evaluating the expression to each row of the table	table expression
VARX.S	Returns the variance of the entire population by evaluating the expression to each row of the table	table expression
XIRR	Returns' the internal rate of return	table values dates guess
XNPV	Returns the net present value	table values dates rates

DAX Functions

Date & Time Function

Filter Function

Information Function

Math & TrigFunction

Statistical Function

Text Function

Logical Function

Parent & Child Function