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.

Date & Time Function

EDATE Returns a date start_datemonths
EOMONTH Returns a date start_datemonths
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 datereturn_type
WEEKNUM Returns integer numbers of week from year datereturn_type
YEAR Returns integer numbers ranging between 1900-9999 date
CLOSINGBALANCEMONTH Returns a scalar value expressiondatesfilter
CLOSINGBALANCEYEAR Returns a scalar value expressiondatesfilteryear_end_date
CLOSINGBALANCEQUARTER Returns a scalar value expressiondatesfilter
DATEADD Returns date values datesnumber_of_intervalsinterval
DATESBETWEEN Returns date values datesstart_dateend_date
LASTDATE Returns date values dates
LASTNONBLANK Returns the last value for the specified column columnexpression
LASTNONBLANKVALUE Returns the last value for the specified column columnexpression
NEXTDAY Returns day dates
NEXTMONTH Returns month dates
NEXTQUARTER Returns date dates
NEXTYEAR Returns a year datesyear_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 datesyear_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 datesnumber_of_intervalsinteval
FIRSTNONBLANKVALUE Returns the value evaluated by the Expression iterating ColumnName columnexpression
ENDOFYEAR Returns the last date of the year in the current context for the specified column of dates datesyear_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 yearmonthday
DATEDIFF Returns the count of interval boundaries crossed between two dates start_dateend_dateinterval
DATESInPeriod Returns A table containing a single column of date values
datesstart_datenumber_of_intervalsinterval
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 datesyear_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 columnexpression
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 expressiondatesfilter
OPENINGBALANCEQUARTER Returns the expression representing firtsr date of the quarter. expressiondatesfilter
OPENINGBALANCEYEAR Returns scalar value that represents the expression evaluated at the first date of the quarter in the current context expressiondatesfilteryear_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 hourminutesecond
TIMEVALUE Returns a date time_text
TOTALMTD Returns the value of the expression for the month to date in the current context expressiondatesfilter
TOTALQTD Returns a scalar value representing the expression calculated for the dates in the current quarter to date expressiondatesfilter
TOTALYTD Returns a scalar value representing the expression calculated for the current year to date expressiondatesfilter
YEARFRAC Calculates the fraction of the year represented by the number of whole days between two dates start_dateend_datebasis

Filter Function

ALL Is useful for clearing filters and creating calculations on all the rows in a table tablecolumncolumn
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 nametypenametypedata
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 ColumnNamenumber
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_expression1table_expression2
FILTER Returns a table with filtered row tablefilter
FILTERS Returns a column of unique values ColumnName
GENERATE Returns a crossjoin of first table table1table2
GENERATEALL Returns an entire table or a table with one or more columns table1table2
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. tableColumnName1nameexpression
IGNORE Does not return any value measure_expression
INTERSECT Returns the row intersection of two tables table_expression1table_expression2
ISONORAFTER Returns TRUE if any of the comparisons is TRUE, otherwise returns FALSE value1value2order
ADDCOLUMNS Used to add a calculated column in a table tablenameexpression
ADDMISSINGITEMS Used to add missing items from other columns ShowAll_ColumnNameTableNameGroupBy_ColumnNameFilterTable
ALLEXCEPT Used to remove context filters in the table tablecolumn
ALLNOBLANKROW Used to return rows that are not blank tablecolumncolumns
ALLSELECTED Used to display the rows and columns of the table TableNameColumnNameColumnName
CALCULATE Used to calculate an expression in the current context expressionfilter1filter2
CALCULATEABLE Used to classify a table expression in the current context expressionfilter1filter2
CALENDAR Used to add a date column start_dateend_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 ColumnName1ColumnName2direction
CROSSJOIN Used to crossjoin the rows and columns and bring out similar values tabletable
KEEPFILTERS Modifies how filters are applied while evaluating a CALCULATE or CALCULATETABLE function expression
LOOKUPVALUE Retrieves a value from a table result_ColumnNameSearch_ColumnNameSearch_value
NATURALINNERJOIN Performs an inner join of a table with another table LeftJoinTableRightJoinTable
NATURALLEFTOUTERJOIN Performs an outer join of a table with another table LeftJoinTableRightJoinTable
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_ColumnNameGroupBy_ColumnName
ROLLUPADDISSUBTOTAL Used exclusively within SUMMARIZECOLUMNS GrandtotalFilterGroupBy_ColumnNameGroupLevelFilter
ROLLUPGROUP Used exclusively within SUMMARIZE or ADDMISSINGITEMS GroupBy_ColumnNameGroupBy_ColumnName
ROLLUPISSUBTOTAL Returns only marks a subset of columns to ADDMISSINGITEMS GrandTotalFilterGroupBy_ColumnNamesSubtotal_ColumnNameGroupLevelFilter
ROW Returns a single row table with new columns  nameexpression
SAMPLE Returns a sample of N rows from the specified table n_valuetableOrderBy_expressionorder
SUBSTITUTEWITHINDEX Returns a table which represents a left semijoin of the two tables supplied as arguments tableIndexColumnNameIndexColumnsTableOrderBy_expressionorder
SUMMARIZE Returns a summary table for the requested totals over a set of groups tableGroupBy_ColumnNamenameexpression
SUMMARIZECOLUMNS Returns a summary table over a set of groups GroupBy_ColumnNameFilterTablenameexpression
TOPN Returns to get Top value from the specified table user n_valuetableOrderBy_expressionOrder
UNION U to join the table from a pair of tables table_expression1table_expression2table_expression
USERELATIONSHIP Used to get specifies the relationship to be used in a specific calculation that has existed between columnName1 and columnName2 ColumnName1ColumnName2
VALUES Used in this function is the column name, a single-column table TableName
SELECTCOLUMNS Returns the calculated columns in the table tablenamescalar_expression

Information Function

Math & TrigFunction

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 numbersignificance
COMBIN Returns a number of combinations for a given number of items numbernumber_chosen
COMBINA Returns a number of combinations for a given number of items numbernumber_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 numeratordenominatoralternateresult
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 tableexpression
FLOOR Returns the number down to requested significance numbersignificance
GCD Returns the greatest common divisor of two or more integers number1number2
ISO.CEILING Returns a number, of the same type as the number argument, rounded as specified numbersignificance
LCM Returns the least common multiple of integers number1number2
LN Returns the natural logarithm of the number passed as an argument number
LOG Returns the logarithm of number to the base specified numberbase
LOG10 Returns the logarithm of number to base 10 number
MOD Returns an integer number numberdivision
MROUND Returns a rounded decimal number numbermultiple
ODD Returns number rounded up to the nearest odd integer number
FACT Returns the factorial of a number, equal to the series 1*2*3*…* , ending in the given number number
PI Returns the value of p, 3.14159265358979, accurate to 15 digits
POWER Returns a decimal number numberpower
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 tableexpression
QUOTIENT Returns a whole number numeratordenominator
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 XalphabetacumulativeAB
BETA.INV Returns the inverse of the beta cumulative probability density function (BETA.DIST) probabilityalphabetaAB
CHISQ.DIST Returns the chi-squared distribution Xdeg_freedomcumulative
CHISQ.DIST.RT Returns the right-tailed probability of the chi-squared distribution Xdeg_freedom
CHISQ.INV Returns the inverse of the left-tailed probability of the chi-squared distribution probabilitydeg_freedom
CHISQ.INV.RT Returns the inverse of the right-tailed probability of the chi-squared distribution probabilitydeg_freedom
CONFIDENCE.NORM Returns the confidence interval for a population mean, using a normal distribution alphastandard_devsize
CONFIDENCE.T This function uses students t distribution and returns confidence interval alphastandard_devsize
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 Xlambdacumulative
PERMUT Returns the number of permutations for a given number of objects that can be selected from a number of objects numbernumber_choosen
POISSON.DIST Returns the Poisson distribution Xmeancumulative
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 bottomtop
ROUND Is used to round a number to a specific digit numbernum_digits
ROUNDDOWN Returns a decimal value numbernum_digits
ROUNDUP Is used to round a number numbernum_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 numbernum_digits

Statistical Function

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 tableexpression
GEOMEANX Returns the geometric mean of an expression evaluated for each row in a table tableexpression
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 tableexpression
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 tableexpression
MIN Returns the smallest value columnexpression1expression2
COUNTAX Returns to count the number of cells in a table tableexpression
COUNTBLANK Returns to count blank cells column
MINA Returns the smallest value column
MINX Returns the smallest value tableexpression
PERCENTILE.EXC Returns the k-th (exclusive) percentile of values in a column columnK
COUNTROWS Returns to count a number of rows table
COUNTX Returns to count a number of rows that are not blank tableexpression
DISTINCTCOUNT Returns to count distinct values column
PERCENTILE.INC Returns the kth percentile value columnK
PERCENTILEX.EXC Returns the kth percentile value of expression evaluated in a table tableexpression
PERCENTILEX.INC Returns the percentile value of expression evaluated tableexpressionK
RANK.EQ Returns to rank a number from a list of numbers valueColumnNameorder
RANKX Returns to rank a number from a list of numbers through an expression tableexpressionvalueorderties
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 tableexpression
STDEVX.P Returns the standard deviation of the entire population by evaluating the expression to each row of the table tableexpression
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 tableexpression
VARX.S Returns the variance of the entire population by evaluating the expression to each row of the table tableexpression
XIRR Returns' the internal rate of return tablevaluesdatesguess
XNPV Returns the net present value tablevaluesdatesrates

Text Function

LEFT Returns stated number characters textnum_chars
BLANK Returns the blank value does not have a data type
CODE Returns a numeric code for the first character in a text string text
COMBINEVALUES Returns the concatenated string Delimiterexpression1expression2
CONCATENATE Returns the concatenated string text1text2
CONCATENATEX Returns text string with the concatenated values tableexpressiondelimeter
EXACT Returns TRUE or FALSE. EXACT is case-sensitive text1text2
FIND Returns number that shows the starting point of the text string you want to find find_textwithin_textstart_numNotFoundValue
FIXED Returns a number represented as text numberdecimalsno_commas
FORMAT Returns a string containing value formatted as defined by the format string valueformat_string
RIGHT Returns a text string containing the specified right-most characters textnum_chars
LOWER Returns a text in lowercase text
REPLACE Returns the resulting string after applying the replacements old_textstart_numnum_charsnew_text
LEN Returns the number of characters in a text string text
MID Returns a string of text of the specified length textstart_numnum_chars
REPT Returns a string such a number of times as specified textnum_times
SEARCH Returns the number of the character find_textwithin_texttart_numNotFoundValue
SUBSTITUTE Returns the modified text string i.e replaced one textold_textnew_textinstance_num
TRIM Returns a text string with spaces removed text
UNICHAR Returns Unicode character referenced by the numeric value number
UPPER Returns the text in uppercase text
VALUE Returns the converted text string that represents a number to a number text
UNICODE Returns the number corresponding to the first character of the text text

Logical Function

Parent & Child Function