DAX

FUNCTIONS
COMPLETE CHEATSHEET
 FUNCTIONS

TIME INTELLIGENCE FUNCTIONS MATH AND TRIG FUNCTIONS

FILTER FUNCTIONS PARENT AND CHILD FUNCTIONS

DATE AND TIME FUNCTIONS STATISTICAL FUNCTIONS

INFORMATION FUNCTIONS TEXT FUNCTIONS

LOGICAL FUNCTIONS OTHER FUNCTIONS

 TIME INTELLIGENCE FUNCTIONS
Function Description Syntax Link

TOTALYTD Function (DAX) Evaluates the year-to-date value of the TOTALYTD(<expression>,<dates>[,<filter>]

expression in the current context. [,<year_end_date>])

TOTALQTD Function (DAX) Evaluates the value of the expression for the TOTALQTD(<expression>,<dates>[,<filter>])
dates in the quarter to date, in the current
context.

TOTALMTD Function (DAX) Evaluates the value of the expression for the TOTALMTD(<expression>,<dates>[,<filter>])
month to date, in the current context.

STARTOFYEAR Function (DAX) Returns the first date of the year in the current STARTOFYEAR(<dates>)
context for the specified column of dates.

STARTOFQUARTER Function (DAX) Returns the first date of the quarter in the current STARTOFQUARTER(<dates>)
context for the specified column of dates.

STARTOFMONTH Function (DAX) Returns the first date of the month in the current STARTOFMONTH(<dates>)
context for the specified column of dates.

SAMEPERIODLASTYEAR Function Returns a table that contains a column of dates SAMEPERIODLASTYEAR(<dates>)

(DAX) shifted one year back in time from the dates in the
specified dates column, in the current context.

PREVIOUSYEAR Function (DAX) Returns a table that contains a column of all dates PREVIOUSYEAR(<dates>[,<year_end_date>])
from the previous year, given the last date in the
dates column, in the current context.

PREVIOUSQUARTER Function (DAX) Returns a table that contains a column of all dates PREVIOUSQUARTER(<dates>)
from the previous quarter, based on the first date
in the dates column, in the current context.

PREVIOUSMONTH Function (DAX) Returns a table that contains a column of all dates PREVIOUSMONTH(<dates>)
from the previous month, based on the first date
in the dates column, in the current context.

PREVIOUSDAY Function (DAX) Returns a table that contains a column of all dates PREVIOUSDAY(<dates>)
representing the day that is previous to the first
date in the dates column, in the current context.

PARALLELPERIOD Function (DAX) Returns a table that contains a column of dates PARALLELPERIOD(<dates>,<number_of_inter-
that represents a period parallel to the dates in the vals>,<interval>)
specified dates column, in the current context,
with the dates shifted a number of intervals either
forward in time or back in time.

OPENINGBALANCEYEAR Function Evaluates the expression at the first date OPENINGBALANCEYEAR(<expression>,<dates>

(DAX) of the year in the current context. [,<filter>][,<year_end_date>])

OPENINGBALANCEQUARTER Function Evaluates the expression at the first date of OPENINGBALANCEQUARTER(<expression>,<dates>

(DAX) the quarter, in the current context. [,<filter>])

OPENINGBALANCEMONTH Function Evaluates the expression at the first date of the OPENINGBALANCEMONTH(<expression>,<dates>
(DAX) month in the current context. [,<filter>])

NEXTYEAR Function (DAX) Returns a table that contains a column of all NEXTYEAR(<dates>[,<year_end_date>])
dates in the next year, based on the first date in
the dates column, in the current context.

NEXTQUARTER Function (DAX) Returns a table that contains a column of all dates NEXTQUARTER(<dates>)
in the next quarter, based on the first date speci-
fied in the dates column, in the current context.

NEXTMONTH Function (DAX) Returns a table that contains a column of all NEXTMONTH(<dates>)
dates from the next month, based on the first
date in the dates column in the current context.

NEXTDAY Function (DAX) Returns a table that contains a column of all dates NEXTDAY(<dates>)
from the next day, based on the first date speci-
fied in the dates column in the current context.

LASTNONBLANK Function (DAX) Returns the last value in the column, column, LASTNONBLANK(<column>,<expression>)
filtered by the current context, where the expres-
sion is not blank.

LASTDATE Function (DAX) Returns the last date in the current context for LASTDATE(<dates>)
the specified column of dates.

FIRSTNONBLANK Function (DAX) Returns the first value in the column, column, FIRSTNONBLANK(<column>,<expression>)
filtered by the current context, where the
expression is not blank.

FIRSTDATE Function (DAX) Returns the first date in the current context for FIRSTDATE(<dates>)
the specified column of dates.

ENDOFYEAR Function (DAX) Returns the last date of the year in the current ENDOFYEAR(<dates> [,<year_end_date>])
context for the specified column of dates.

ENDOFQUARTER Function (DAX) Returns the last date of the quarter in the current ENDOFQUARTER(<dates>)
context for the specified column of dates.

ENDOFMONTH Function (DAX) Returns the last date of the month in the current ENDOFMONTH(<dates>)
context for the specified column of dates.

DATESYTD Function (DAX) Returns a table that contains a column of the DATESYTD(<dates> [,<year_end_date>])
dates for the year to date, in the current context.

DATESQTD Function (DAX) Returns a table that contains a column of the DATESQTD(<dates>)
dates for the quarter to date, in the current
context.

DATESMTD Function (DAX) Returns a table that contains a column of the DATESMTD(<dates>)
dates for the month to date, in the current
context.

DATESINPERIOD Function (DAX) Returns a table that contains a column of dates DATESINPERIOD(<dates>,<start_date>,
that begins with the start_date and continues for <number_of_intervals>,<interval>)
the specified number_of_intervals.

DATESBETWEEN Function (DAX) Returns a table that contains a column of dates DATESBETWEEN(<dates>,<start_date>,
that begins with the start_date and continues <end_date>)
until the end_date.

DATEADD Function (DAX) Returns a table that contains a column of dates, DATEADD(<dates>,<number_of_intervals>,
shifted either forward or backward in time by the <interval>)
specified number of intervals from the dates in
the current context.

CLOSINGBALANCEYEAR Function Evaluates the expression at the last date of the CLOSINGBALANCEYEAR(<expression>,<dates>
(DAX) year in the current context. [,<filter>][,<year_end_date>])

CLOSINGBALANCEQUARTER Function Evaluates the expression at the last date of the CLOSINGBALANCEQUARTER(<expression>,
(DAX) quarter in the current context. <dates>[,<filter>])

CLOSINGBALANCEMONTH Function Evaluates the expression at the last date of the CLOSINGBALANCEMONTH(<expression>,
(DAX) month in the current context. <dates>[,<filter>])
 FILTER FUNCTIONS
Function Description Syntax Link

VALUES Function (DAX) Returns a one-column table that contains the VALUES(<TableNameOrColumnName>)
distinct values from the specified table or column.
In other words, duplicate values are removed and
only unique values are returned.

USERELATIONSHIP Function (DAX) Specifies the relationship to be used in a USERELATIONSHIP(<columnName1>,

specific calculation as the one that exists <columnName2>)
between columnName1 and columnName2.

SUBSTITUTEWITHINDEX Returns a table which represents a left semijoin of SUBSTITUTEWITHINDEX(<table>, <index-

Function (DAX) the two tables supplied as arguments. The semi- ColumnName>, <indexColumnsTable>, [<or-
derBy_expression>, [<order>][, <order-
join is performed by using common columns,
By_expression>, [<order>]]…])
determined by common column names and
common data type . The columns being joined on
are replaced with a single column in the returned
table which is of type integer and contains an
index. The index is a reference into the right join
table given a specified sort order.

SELECTEDVALUE Function (DAX) Returns the value when the context for column- SELECTEDVALUE(<columnName>[, <alterna-
Name has been filtered down to one distinct teResult>])
value only. Otherwise returns alternateResult.

RELATEDTABLE Function (DAX) Evaluates a table expression in a context RELATEDTABLE(<tableName>)

modified by the given filters.

RELATED Function (DAX) Returns a related value from another table. RELATED(<column>)

KEEPFILTERS Function (DAX) Modifies how filters are applied while evaluating KEEPFILTERS(<expression>)
a CALCULATE or CALCULATETABLE function.

ISFILTERED Function (DAX) Returns TRUE when columnName is being ISFILTERED(<columnName>)

filtered directly. If there is no filter on the column
or if the filtering happens because a different
column in the same table or in a related table is
being filtered then the function returns FALSE.

ISCROSSFILTERED Function (DAX) Returns TRUE when columnName or ISCROSSFILTERED(<columnName>)

another column in the same or related
table is being filtered.

HASONEVALUE Function (DAX) Returns TRUE when the context for columnName HASONEVALUE(<columnName>)
has been filtered down to one distinct value only.
Otherwise is FALSE.

HASONEFILTER Function (DAX) Returns TRUE when the number of directly

filtered values on columnName is one.

FILTERS Function (DAX) Returns the values that are directly applied as FILTERS(<columnName>)
filters to columnName.

FILTER Function (DAX) Returns a table that represents a subset FILTER(<table>,<filter>)

of another table or expression.

EARLIEST Function (DAX) Returns the current value of the specified EARLIEST(<column>)
column in an outer evaluation pass of the
specified column.

EARLIER Function (DAX) Returns the current value of the specified column EARLIER(<column>, <number>)
in an outer evaluation pass of the mentioned
column.

DISTINCT Function (DAX) Returns a one-column table that contains the DISTINCT(<column>)
distinct values from the specified column. In
other words, duplicate values are removed and
only unique values are returned.

CROSSFILTER Function Specifies the cross-filtering direction to be used CROSSFILTER(<columnName1>,

in a calculation for a relationship that exists <columnName2>, <direction>)
between two columns.

CALCULATETABLE Function (DAX) Evaluates a table expression in a context modi- CALCULATETABLE(<expression>,

fied by the given filters. <filter1>,<filter2>,…)

CALCULATE Function (DAX) Evaluates an expression in a context that is CALCULATE(<expression>,<filter1>,

modified by the specified filters. <filter2>…)

ALLSELECTED Function (DAX) Removes context filters from columns and rows in ALLSELECTED([<tableName> | <columnName>])
the current query, while retaining all other context
filters or explicit filters.

ALLNOBLANKROW Function (DAX) From the parent table of a relationship, returns all ALLNOBLANKROW( {<table> | <column>[,
rows but the blank row, or all distinct values of a <column>[, <column>[,…]]]} )
column but the blank row, and disregards any
context filters that might exist.

ALL Function (DAX) Returns all the rows in a table, or all the values in ALL( {<table> | <column>[, <column>[,
a column, ignoring any filters that might have <column>[,…]]]} )
been applied. This function is useful for clearing
filters and creating calculations on all the rows
in a table.

ALLEXCEPT Function (DAX) Removes all context filters in the table except ALLEXCEPT(<table>,<column>[,<column>[,…]])
filters that have been applied to the specified
columns.

ADDMISSINGITEMS Function (DAX) Adds combinations of items from multiple ADDMISSINGITEMS(<showAllColumn>[, <show-
columns to a table if they do not already exist. AllColumn>]…, <table>, <groupingColumn>[,
<groupingColumn>]…[, filterTable]…)
The determination of which item combina-
tions to add is based on referencing source
columns which contain all the possible values
for the columns.
 DATE AND TIME FUNCTIONS
Function Description Syntax Link

CALENDARAUTO Function (DAX) Returns a table with a single column named CALENDARAUTO([fiscal_year_end_month])
“Date” that contains a contiguous set of dates.
The range of dates is calculated automatically
based on data in the model.

CALENDAR Function (DAX) Returns a table with a single column named CALENDAR(<start_date>, <end_date>)
“Date” that contains a contiguous set of dates.
The range of dates is from the specified start date
to the specified end date, inclusive of those two
dates.

DATEDIFF Function (DAX) Returns the count of interval boundaries crossed DATEDIFF(<start_date>, <end_date>,
between two dates. <interval>)

DATE Function (DAX) Returns the specified date in datetime format. DATE(<year>, <month>, <day>)

DATEVALUE Function (DAX) Converts a date in the form of text to a DATEVALUE(date_text)

date in datetime format.

DAY Function (DAX) Returns the day of the month, a number DAY(<date>)
from 1 to 31.

EDATE Function (DAX) Returns the date that is the indicated number EDATE(<start_date>, <months>)
of months before or after the start date. Use
EDATE to calculate maturity dates or due
dates that fall on the same day of the month
as the date of issue.

EOMONTH Function (DAX) Returns the date in datetime format of the last EOMONTH(<start_date>, <months>)
day of the month, before or after a specified
number of months. Use EOMONTH to calculate
maturity dates or due dates that fall on the last
day of the month.

HOUR Function (DAX) Returns the hour as a number from 0 (12:00 A.M.) HOUR(<datetime>)
to 23 (11:00 P.M.).

MINUTE Function (DAX) Returns the minute as a number from 0 to 59, MINUTE(<datetime>)
given a date and time value.

MONTH Function (DAX) Returns the month as a number from 1 (Janu- MONTH(<datetime>)
ary) to 12 (December).

NOW Function (DAX) Returns the current date and time in datetime NOW()
format.

SECOND Function (DAX) Returns the seconds of a time value, as a SECOND(<time>)

number from 0 to 59.

TIME Function (DAX) Converts hours, minutes, and seconds given TIME(hour, minute, second)
as numbers to a time in datetime format.

TIMEVALUE Function (DAX) Converts a time in text format to a time in date- TIMEVALUE(time_text)
time format.

TODAY Function (DAX) Returns the current date. TODAY()

WEEKDAY Function (DAX) Returns a number from 1 to 7 identifying the day WEEKDAY(<date>, <return_-
of the week of a date. By default the day ranges type>)
from 1 (Sunday) to 7 (Saturday).

WEEKNUM Function (DAX) Returns the week number for the given date WEEKNUM(<date>, <return_type>)
and year according to the return_type value.
The week number indicates where the week
falls numerically within a year.

YEARFRAC Function (DAX) Calculates the fraction of the year represented by YEARFRAC(<start_date>, <end_date>,
the number of whole days between two dates. <basis>)
Use the YEARFRAC worksheet function to identi-
fy the proportion of a whole year's benefits or
obligations to assign to a specific term.

YEAR Function (DAX) Returns the year of a date as a four digit integer in YEAR(<date>)
the range 1900-9999.
 INFORMATION FUNCTIONS
Function Description Syntax Link

USERNAME Function (DAX) Returns the domain name and username from USERNAME()
the credentials given to the system at connec-
tion time

LOOKUPVALUE Function (DAX) Returns the value in result_columnName for LOOKUPVALUE(<result_columnName>,

the row that meets all criteria specified by <search_columnName>,<searc
search_columnName and search_value.

ISTEXT Function (DAX) Checks if a value is text, and returns ISTEXT(<value>)

TRUE or FALSE.

ISONORAFTER Function (DAX) A boolean function that emulates the behavior ISONORAFTER(<scalar_expression>, <sca-
of a ‘Start At’ clause and returns true for a row lar_expression>[, sort_order [, <sca-
lar_expression>, <scalar_expression>[,
that meets all of the condition parameters.
sort_order]]…)

ISNUMBER Function (DAX) Checks whether a value is a number, and ISNUMBER(<value>)

returns TRUE or FALSE.

ISNONTEXT Function (DAX) Checks if a value is not text (blank cells are ISNONTEXT(<value>)
not text), and returns TRUE or FALSE.

ISLOGICAL Function (DAX) Checks whether a value is a logical value, (TRUE ISLOGICAL(<value>)
or FALSE), and returns TRUE or FALSE.

ISEVEN Function (DAX) Returns TRUE if number is even, or FALSE if ISEVEN(number)

number is odd.

ISERROR Function (DAX) Checks whether a value is an error, and returns ISERROR(<value>)
TRUE or FALSE.

ISBLANK Function (DAX) Checks whether a value is blank, and returns ISBLANK(<value>)
TRUE or FALSE.

CUSTOMDATA Function (DAX) Returns the content of the CustomData prop- CUSTOMDATA()
erty in the connection string.

CONTAINS Function (DAX) Returns true if values for all referred columns CONTAINS(<table>, <columnName>, <value>
exist, or are contained, in those columns. [, <columnName>, <value>]…)
 LOGICAL FUNCTIONS
Function Description Syntax Link

TRUE Function (DAX) Returns the logical value TRUE. TRUE()

SWITCH Function (DAX) Evaluates an expression against a list of SWITCH(<expression>, <value>,

values and returns one of multiple possible <result>[, <value>, <result>]…[, <else>])
result expressions.

OR Function (DAX) Checks whether one of the argu ments is OR(<logical1>,<logical2>)

TRUE to return TRUE. The function returns
FALSE if both arguments are FALSE.

NOT Function (DAX) Changes FALSE to TRUE, or TRUE to FALSE. NOT(<logical>)

IF Function (DAX) Checks if a condition provided as the first IF(logical_test>,<val-

argument is met. Returns one value if the ue_if_true>, value_if_false)
condition is TRUE, and returns another
value if the condition is FALSE.

IFERROR Function (DAX) Evaluates an expression and returns a speci

fied value if the expression returns an error

FALSE Function (DAX) Returns the logical value FALSE. FALSE()

AND Function (DAX) Checks whether both arguments are TRUE, AND(<logical1>,<logical2>)
and returns TRUE if both arguments are
TRUE. Otherwise returns false.
 MATH AND TRIG FUNCTIONS
Function Description Syntax Link

TRUNC Function (DAX) Truncates a number to an integer by removing TRUNC(<number>,<num_digits>)

the decimal, or fractional, part of the number.

SUMX Function (DAX) Returns the sum of an expression evaluated for SUMX(<table>, <expression>)
each row in a table.

SUM Function (DAX) Adds all the numbers in a column. SUM(<column>)

SQRT Function (DAX) Returns the square root of a number. SQRT(<number>)

SIGN Function (DAX) Determines the sign of a number, the result of a SIGN(<number>)t
calculation, or a value in a column. The function
returns 1 if the number is positive, 0 (zero) if the
number is zero, or -1 if the number is negative.

ROUNDUP Function (DAX) Rounds a number up, away from 0 (zero). ROUNDUP(<number>, <num_digits>)

ROUND Function (DAX) Rounds a number to the specified number ROUND(<number>, <num_digits>)
of digits.

ROUNDDOWN Function (DAX) Rounds a number down, toward zero. ROUNDDOWN(<number>, <num_digits>)

RAND Function (DAX) Returns a random number greater than or equal RAND()
to 0 and less than 1, evenly distributed. The
number that is returned changes each time the
cell containing this function is recalculated.

RANDBETWEEN Function (DAX) Returns a random number in the range between RANDBETWEEN(<bottom>,<top>)
two numbers you specify.

RADIANS Function (DAX) Converts degrees to radians. RADIANS(angle)

QUOTIENT Function (DAX) Performs division and returns only the integer por- QUOTIENT(<numerator>, <denominator>)
tion of the division result. Use this function when
you want to discard the remainder of division.

PRODUCTX Function (DAX) Returns the product of an expression evaluated PRODUCTX(<table>, <expression>)
for each row in a table.

PRODUCT Function (DAX) Returns the product of the numbers in a column. PRODUCT(<column>)

POWER Function (DAX) Returns the result of a number raised to a power. POWER(<number>, <power>)

PI Function (DAX) Returns the value of Pi, 3.14159265358979, PI()

accurate to 15 digits.

ODD Function (DAX) Returns number rounded up to the nearest odd ODD(number)
integer.

MROUND Function (DAX) Returns a number rounded to the desired MROUND(<number>, <multiple>)
multiple.

LOG Function (DAX) Returns the logarithm of a number to the base LOG(<number>,<base>)
you specify.

LOG10 Function (DAX) Returns the base-10 logarithm of a number. LOG10(<number>)

LN Function (DAX) Returns the natural logarithm of a number. LN(<number>)

Natural logarithms are based on the constant
e (2.71828182845904).

LCM Function (DAX) Returns the least common multiple of integers. LCM(number1, [number2], ...)
The least common multiple is the smallest posi-
tive integer that is a multiple of all integer argu-
ments number1, number2, and so on. Use LCM to
add fractions with different denominators.

ISO.CEILING Function (DAX) Rounds a number up, to the nearest integer or to ISO.CEILING(<number>[, <significance>])
the nearest multiple of significance.

INT Function (DAX) Rounds a number down to the nearest integer. INT(<number>)

GCD Function (DAX) Returns the greatest common divisor of two or GCD(number1, [number2], ...)
more integers. The greatest common divisor is the
largest integer that divides both number1 and
number2 without a remainder.

FLOOR Function (DAX) Rounds a number down, toward zero, to the FLOOR(<number>, <significance>)
nearest multiple of significance.

FACT Function (DAX) Returns the factorial of a number, equal to the FACT(<number>)
series 1*2*3*...* , ending in the given number.

EXP Function (DAX) Returns e raised to the power of a given number. EXP(<number>)
The constant e equals 2.71828182845904, the base
of the natural logarithm.

EVEN Function (DAX) Returns number rounded up to the nearest even EVEN(number)
integer. You can use this function for processing
items that come in twos. For example, a packing
crate accepts rows of one or two items. The crate
is full when the number of items, rounded up to
the nearest two, matches the crate's capacity.

DIVIDE Function (DAX) Performs division and returns alternate result or DIVIDE(<numerator>, <denominator>
BLANK() on division by 0. [,<alternateresult>])

DEGREES Function (DAX) Converts radians into degrees. DEGREES(angle)

CURRENCY Function (DAX) Evaluates the argument and returns the CURRENCY(<value>)
result as currency data type.

COSH Function (DAX) Returns the hyperbolic cosine of a number. COSH(number)

COS Function (DAX) Returns the cosine of the given angle. COS(number)

COMBIN Function (DAX) Returns the number of combinations for a given COMBIN(number, number_chosen)
number of items. Use COMBIN to determine the
total possible number of groups for a given
number of items.

COMBINA Function (DAX) Returns the number of combinations (with repeti- COMBINA(number, number_chosen)
tions) for a given number of items.

CEILING Function (DAX) Rounds a number up, to the nearest integer or to CEILING(<number>, <significance>)
the nearest multiple of significance.

ATANH Function (DAX) Returns the inverse hyperbolic tangent of a ATANH(number)

number. Number must be between -1 and 1
(excluding -1 and 1). The inverse hyperbolic tangent
is the value whose hyperbolic tangent is number,
so ATANH(TANH(number)) equals number.

ATAN Function (DAX) Returns the arctangent, or inverse tangent, of a ATAN(number)

number. The arctangent is the angle whose tan-
gent is number. The returned angle is given in
radians in the range -pi/2 to pi/2.

ASINH Function (DAX) Returns the inverse hyperbolic sine of a number. ASINH(number)
The inverse hyperbolic sine is the value whose
hyperbolic sine is number, so ASINH(SINH(num-
ber)) equals number.

ASIN Function (DAX) Returns the arcsine, or inverse sine, of a number. ASIN(number)
The arcsine is the angle whose sine is number.
The returned angle is given in radians in the
range -pi/2 to pi/2.

ACOSH Function (DAX) Returns the inverse hyperbolic cosine of a number. ACOSH(number)
The number must be greater than or equal to 1.
The inverse hyperbolic cosine is the value whose
hyperbolic cosine is number, so ACOSH(COSH(-
number)) equals number.

ACOS Function (DAX) Returns the arccosine, or inverse cosine, of a ACOS(number)

number. The arccosine is the angle whose cosine
is number. The returned angle is given in radians
in the range 0 (zero) to pi.

ABS Function (DAX) Returns the absolute value of a number. ABS(<number>)

 PARENT AND CHILD FUNCTIONS
Function Description Syntax Link

PATHLENGTH Function (DAX) Returns the number of parents to the speci- PATHLENGTH(<path>)
fied item in a given PATH result, including self.

PATHITEMREVERSE Function (DAX) Returns the item at the specified position from PATHITEMREVERSE(<path>, <position>
a string resulting from evaluation of a PATH [, <type>])
function. Positions are counted backwards
from right to left.

PATHITEM Function (DAX) Returns the item at the specified position from a PATHITEM(<path>, <position>[, <type>])
string resulting from evaluation of a PATH func-
tion. Positions are counted from left to right.

PATH Function (DAX) Returns a delimited text string with the identifiers PATH(<ID_columnName>, <parent_columnName>)
of all the parents of the current identifier, starting
with the oldest and continuing until current.

PATHCONTAINS Function (DAX) Returns TRUE if the specified item exists within PATHCONTAINS(<path>, <item>)
the specified path.
 STATISTICAL FUNCTIONS
Function Description Syntax Link

XNPV Function (DAX) Returns the present value for a schedule of cash XNPV(<table>, <values>, <dates>, <rate>)
flows that is not necessarily periodic.

XIRR Function (DAX) Returns the internal rate of return for a schedule XIRR(<table>, <values>, <dates>, [guess])
of cash flows that is not necessarily periodic.

VARX.S Function (DAX) and n is the population size VARX.S(InternetSales_USD, Internet-

Sales_USD[UnitPrice_USD] – (Internet-
Sales_USD[DiscountAmount_USD]/Internet-
Sales_USD[OrderQuantity]))

VARX.S Function (DAX) Returns the variance of a sample population. VARX.S(<table>, <expression>)

VARX.P Function (DAX) and n is the population size VARX.P(InternetSales_USD, Internet-

Sales_USD[UnitPrice_USD] –(Internet-
Sales_USD[DiscountAmount_USD]/Internet-
Sales_USD[OrderQuantity]))

VARX.P Function (DAX) Returns the variance of the entire population. VARX.P(<table>, <expression>)

VAR.S Function (DAX) and n is the population size VAR.S(InternetSales_USD[SalesAmount_USD])

VAR.S Function (DAX) Returns the variance of a sample population. VAR.S(<columnName>)

VAR.P Function (DAX) and n is the population size VAR.P(InternetSales_USD[SalesAmount_USD])

VAR.P Function (DAX) Returns the variance of the entire population. VAR.P(<columnName>)

TOPN Function (DAX) Returns the top N rows of the specified table. TOPN(<n_value>, <table>, <orderBy_expres-
sion>, [<order>[, <orderBy_expression>,
[<order>]]…])

T.INV Function (DAX) Returns the left-tailed inverse of the Student's T.INV(Probability,Deg_freedom)
t-distribution.

T.INV.2t Function (DAX) Returns the two-tailed inverse of the Student's T.INV.2T(Probability,Deg_freedom)
t-distribution.

T.DIST.RT Function (DAX) Returns the right-tailed Student's t-distribution. T.DIST.RT(X,Deg_freedom)

T.DIST Function (DAX) Returns the Student's left-tailed t-distribution. T.DIST(X,Deg_freedom,Cumulative)

T.DIST.2T Function (DAX) Returns the two-tailed Student's t-distribution. T.DIST.2T(X,Deg_freedom)

TANH Function (DAX) Returns the hyperbolic tangent of a number. TANH(number)

TAN Function (DAX) Returns the tangent of the given angle. TAN(number)

SUMMARIZE Function (DAX) Returns a summary table for the requested totals SUMMARIZE(<table>, <group-
over a set of groups. By_columnName>[, <groupBy_column-
Name>]…[, <name>, <expression>]…)

STDEVX.S Function (DAX) and n is the population size STDEVX.S(RELATEDTABLE(InternetSales_USD),

InternetSales_USD[UnitPrice_USD] – (In-
ternetSales_USD[DiscountAmount_USD]/In-
ternetSales_USD[OrderQuantity]))

STDEVX.S Function (DAX) Returns the standard deviation of a sample STDEVX.S(<table>, <expression>)
population.

STDEVX.P Function (DAX) and n is the population size STDEVX.P(RELATEDTABLE(InternetSales_USD),

InternetSales_USD[UnitPrice_USD] – (Inter-
netSales_USD[DiscountAmount_USD]/Internet-
Sales_USD[OrderQuantity]))

STDEVX.P Function (DAX) Returns the standard deviation of the entire STDEVX.P(<table>, <expression>)
population.

STDEV.S Function (DAX) and n is the population size STDEV.S(InternetSales_USD[SalesAmount_USD])

STDEV.S Function (DAX) Returns the standard deviation of a sample STDEV.S(<ColumnName>)

population.

STDEV.P Function (DAX) and n is the population size STDEV.P(InternetSales_USD[SalesAmount_USD])

STDEV.P Function (DAX) Returns the standard deviation of the entire STDEV.P(<ColumnName>)
population.

SQRTPI Function (DAX) Returns the square root of (number * pi). SQRTPI(number)

SINH Function (DAX) Returns the hyperbolic sine of a number. SINH(number)

SIN Function (DAX) Returns the sine of the given angle. SIN(number)

SELECTCOLUMNS Function (DAX) Adds calculated columns to the given table or SELECTCOLUMNS(<table>, <name>,
table expression. <scalar_expression> [, <name>,
<scalar_expression>]…)

SAMPLE Function (DAX) Returns a sample of N rows from the specified SAMPLE(<n_value>, <table>, <orderBy_ex-
table. pression>, [<order>[, <orderBy_expres-
sion>, [<order>]]…])

ROW Function (DAX) Returns a table with a single row containing ROW(<name>, <expression>[[,<name>,
values that result from the expressions given to <expression>]…])
each column.

RANKX Function (DAX) Returns the ranking of a number in a list of RANKX(<table>, <expression>[, <value>[,
numbers for each row in the table argument. <order>[, <ties>]]])

RANK.EQ Function (DAX) Returns the ranking of a number in a list RANK.EQ(<value>, <columnName>[,
of numbers. <order>])

POISSON.DIST Function (DAX) Returns the Poisson distribution. A common appli- POISSON.DIST(x,mean,cumulative)
cation of the Poisson distribution is predicting the
number of events over a specific time, such as the
number of cars arriving at a toll plaza in 1 minute.

PERCENTILEX.INC Function (DAX) Returns the percentile number of an expression PERCENTILEX.INC(<table>, <expression>
evaluated for each row in a table.

PERCENTILEX.EXC Function (DAX) Returns the percentile number of an expression PERCENTILEX.EXC(<table>, <expression>, k)
evaluated for each row in a table.

PERCENTILE.INC Function (DAX) Returns the k-th percentile of values in a range, PERCENTILE.INC(<column>, <k>)
where k is in the range 0..1, inclusive.

PERCENTILE.EXC Function (DAX) Returns the k-th percentile of values in a range, PERCENTILE.EXC(<column>, <k>)
where k is in the range 0..1, exclusive.

NORM.S.INV (DAX) Returns the inverse of the standard normal NORM.S.INV(Probability)

cumulative distribution. The distribution has a
mean of zero and a standard deviation of one.

NORM.S.DIST Function (DAX) Returns the standard normal distribution (has a NORM.S.DIST(Z, Cumulative)
mean of zero and a standard deviation of one).

NORM.INV Function (DAX) The inverse of the normal cumulative distribution NORM.INV(Probability, Mean, Standard_dev)
for the specified mean and standard deviation.

NORM.DIST Function (DAX) Returns the normal distribution for the specified NORM.DIST(X, Mean, Standard_dev,
mean and standard deviation. Cumulative)

MINX Function (DAX) Returns the smallest numeric value that results MINX(<table>, < expression>)
from evaluating an expression for each row of a
table.

MIN Function (DAX) Returns the smallest numeric value in a MIN(<column>)

column, or between two scalar expressions.
Ignores logical values and text.

MINA Function (DAX) Returns the smallest value in a column, MINA(<column>)

including any logical values and numbers
represented as text.

MEDIANX Function (DAX) Returns the median number of an expression MEDIANX(<table>, <expression>)
evaluated for each row in a table.

MEDIAN Function (DAX) Returns the median of numbers in a column. MEDIAN(<column>)

MAXX Function (DAX) Evaluates an expression for each row of a table MAXX(<table>,<expression>)
and returns the largest numeric value.

MAX Function (DAX) Returns the largest numeric value in a column, MAX(<column>)
or between two scalar expressions.

MAXA Function (DAX) Returns the largest value in a column. Logical MAXA(<column>)
values and blanks are counted.

GEOMEANX Function (DAX) Returns the geometric mean of an expression GEOMEANX(<table>, <expression>)
evaluated for each row in a table.

GEOMEAN Function (DAX) Returns the geometric mean of the numbers GEOMEAN(<column>)
in a column.

GENERATE Function (DAX) Returns a table with the Cartesian product GENERATE(<table1>, <table2>)
between each row in table1 and the table that
results from evaluating table2 in the context of
the current row from table1.

GENERATEALL Function (DAX) Returns a table with the Cartesian product GENERATEALL(<table1>, <table2>)
between each row in table1 and the table that
results from evaluating table2 in the context of
the current row from table1.

EXPON.DIST Function (DAX) Returns the exponential distribution. Use EXPON.DIST(x,lambda,cumulative)

EXPON.DIST to model the time between events,
such as how long an automated bank teller takes
to deliver cash. For example, you can use
EXPON.DIST to determine the probability that the
process takes at most 1 minute.

DISTINCTCOUNT Function (DAX) The DISTINCTCOUNT function counts the DISTINCTCOUNT(<column>)

number of distinct values in a column.

DATATABLE Function Provides a mechanism for declaring an inline DATATABLE (ColumnName1, DataType1,
set of data values. ColumnName2, DataType2..., {{Value1,
Value2...}, {ValueN, ValueN+1...}...})

CROSSJOIN Function (DAX) Returns a table that contains the Cartesian CROSSJOIN(<table>, <table>[, <table>]…)
product of all rows from all tables in the argu-
ments. The columns in the new table are all
the columns in all the argument tables.

COUNTX(<table>,<expression>)
COUNTX Function (DAX) Counts the number of rows that contain a number
or an expression that evaluates to a number, when
evaluating an expression over a table.

COUNTROWS Function (DAX) The COUNTROWS function counts the number of COUNTROWS(<table>)
rows in the specified table, or in a table defined
by an expression.

COUNT Function (DAX) The COUNT function counts the number of cells COUNT(<column>)
in a column that contain numbers.

COUNTBLANK Function (DAX) Counts the number of blank cells in a column. COUNTBLANK(<column>)

COUNTAX Function (DAX) The COUNTAX function counts nonblank results COUNTAX(<table>,<expression>)
when evaluating the result of an expression over
a table. That is, it works just like the COUNTA
function, but is used to iterate through the rows
in a table and count rows where the specified
expressions results in a nonblank result.

COUNTA Function (DAX) The COUNTA function counts the number of cells COUNTA(<column>)
in a column that are not empty. It counts not just
rows that contain numeric values, but also rows
that contain nonblank values, including text,
dates, and logical values.

CONFIDENCE.T Function (DAX) Returns the confidence interval for a population CONFIDENCE.T(alpha,standard_dev,size)
mean, using a Student's t distribution.

CONFIDENCE.NORM Function (DAX) The confidence interval is a range of values. Your

sample mean, x, is at the center of this range and
the range is x ± CONFIDENCE.NORM. For exam-
ple, if x is the sample mean of delivery times for
products ordered through the mail, x ± CONFI-
DENCE.NORM is a range of population means.
For any population mean, μ0, in this range, the
probability of obtaining a sample mean further
from μ0 than x is greater than alpha

CHISQ.INV.RT Function (DAX) Returns the inverse of the right-tailed probabil- CHISQ.INV.RT(probability,deg_freedom)
ity of the chi-squared distribution.

CHISQ.INV Function (DAX) Returns the inverse of the left-tailed probability CHISQ.INV(probability,deg_freedom)
of the chi-squared distribution.

BETA.INV Function (DAX) Returns the inverse of the beta cumulative BETA.INV(probability,alpha,beta,[A],[B])
probability density function (BETA.DIST).

BETA.DIST Function (DAX) Returns the beta distribution. The beta distribu- BETA.DIST(x,alpha,beta,cumulative,[A],[B])
tion is commonly used to study variation in the
percentage of something across samples, such as
the fraction of the day people spend watching
television.

AVERAGEX Function (DAX) Calculates the average (arithmetic mean) of a set AVERAGEX(<table>,<expression>)
of expressions evaluated over a table.

AVERAGE Function (DAX) Returns the average (arithmetic mean) of all the AVERAGE(<column>)
numbers in a column.

AVERAGEA Function (DAX) Returns the average (arithmetic mean) AVERAGEA(<column>)

of the values in a column. Handles text
and non-numeric values.

ADDCOLUMNS Function (DAX) Adds calculated columns to the given table or ADDCOLUMNS(<table>, <name>, <expres-
table expression. sion>[, <name>, <expression>]…)
 TEXT FUNCTIONS
Function Description Syntax Link

VALUE Function (DAX) Converts a text string that represents a number VALUE(<text>)
to a number.

UPPER Function (DAX) Converts a text string to all uppercase letters. UPPER (<text>)

UNICHAR Function (DAX) Returns the Unicode character referenced by the UNICHAR(number)
numeric value.

TRIM Function (DAX) Removes all spaces from text except for single TRIM(<text>)
spaces between words.

SUBSTITUTE Function (DAX) Replaces existing text with new text in SUBSTITUTE(<text>, <old_text>, <new_text>,
a text string. <instance_num>)

SEARCH Function (DAX) Returns the number of the character at which SEARCH(<find_text>, <within_text>[,
a specific character or text string is first found, [<start_num>][, <NotFoundValue>]])
reading left to right. Search is case-insensitive
and accent sensitive.

RIGHT Function (DAX) RIGHT returns the last character or characters in a RIGHT(<text>, <num_chars>)
text string, based on the number of characters you
specify.

REPT Function (DAX) Repeats text a given number of times. Use REPT REPT(<text>, <num_times>)
to fill a cell with a number of instances of a text
string.

REPLACE Function (DAX) REPLACE replaces part of a text string, based on REPLACE(<old_text>, <start_num>,
the number of characters you specify, with a <num_chars>, <new_text>)
different text string.

MID Function (DAX) Returns a string of characters from the middle of MID(<text>, <start_num>, <num_chars>)
a text string, given a starting position and length.

LOWER Function (DAX) Converts all letters in a text string to lowercase. LOWER(<text>)

LEN Function (DAX) Returns the number of characters in a text string. LEN(<text>)

LEFT Function (DAX) Returns the specified number of characters LEFT(<text>, <num_chars>)
from the start of a text string.

FORMAT Function (DAX) Converts a value to text according to the FORMAT(<value>, <format_string>)
specified format.

FIXED Function (DAX) Rounds a number to the specified number of FIXED(<number>, <decimals>, <no_commas>)
decimals and returns the result as text. You can
specify that the result be returned with or with-
out commas.

FIND Function (DAX) Returns the starting position of one text string FIND(<find_text>, <within_text>[,
within another text string. FIND is case-sensitive. [<start_num>][, <NotFoundValue>]])

EXACT Function (DAX) Compares two text strings and returns TRUE if EXACT(<text1>,<text2>)
they are exactly the same, FALSE otherwise. EXACT
is case-sensitive but ignores formatting differenc-
es. You can use EXACT to test text being entered
into a document.

CONCATENATEX Function (DAX) Concatenates the result of an expression evaluat- CONCATENATEX(<table>, <expression>,
ed for each row in a table. [delimiter])

CONCATENATE Function (DAX) Joins two text strings into one text string. CONCATENATE(<text1>, <text2>)

CODE Function (DAX) Returns a numeric code for the first character in a CODE(text)
text string. The returned code corresponds to the
character set used by your computer.

BLANK Function (DAX) Returns a blank. BLANK()

 OTHER FUNCTIONS
Function Description Syntax Link

UNION Function (DAX) Creates a union (join) table from a pair of tables. UNION(<table_expression1>, <table_expres-
sion2> [,<table_expression>]…)

TREATAS Function (DAX) Applies the result of a table expression as filters to TREATAS(table_expression, <column>[,
columns from an unrelated table. <column>[, <column>[,…]]]} )

SUMMARIZECOLUMNS Function (DAX) Returns a summary table over a set of groups. SUMMARIZECOLUMNS( <groupBy_columnName>
[, < groupBy_columnName >]…, [<filterT-
able>]…[, <name>, <expression>]…)

NATURALLEFTOUTERJOIN Function Performs an inner join of a table with another NATURALLEFTOUTERJOIN(<leftJoinTable>,

(DAX) table. The tables are joined on common columns <rightJoinTable>)
(by name) in the two tables. If the two tables have
no common column names, an error is returned.

NATURALINNERJOIN Function (DAX) Performs an inner join of a table with another NATURALINNERJOIN(<leftJoinTable>,
table. The tables are joined on common columns <rightJoinTable>)
(by name) in the two tables. If the two tables have
no common column names, an error is returned.

ISEMPTY Function (DAX) Checks if a table is empty. ISEMPTY(<table_expression>)

INTERSECT Function (DAX) Returns the row intersection of two tables, INTERSECT(<table_expression1>,
retaining duplicates. <table_expression2>)

GROUPBY Function (DAX) The GROUPBY function is similar to the SUMMA-

RIZE function. However, GROUPBY does not do an
implicit CALCULATE for any extension columns
that it adds. GROUPBY permits a new function,
CURRENTGROUP(), to be used inside aggregation
functions in the extension columns that it adds.
GROUPBY attempts to reuse the data that has
been grouped

GENERATESERIES Function (DAX) Returns a single column table containing the GENERATESERIES(<startValue>, <endValue>[,
values of an arithmetic series, that is, a sequence <incrementValue>])
of values in which each differs from the preced-
ing by a constant quantity. The name of the
column returned is Value.

EXCEPT Function (DAX) Returns the rows of one table which do not EXCEPT(<table_expression1>,
appear in another table. <table_expression2>

ERROR Function (DAX) Raises an error with an error message. ERROR(<text>)

DATATABLE Function (DAX) Provides a mechanism for declaring an inline DATATABLE (ColumnName1, DataType1, Column-
set of data values. Name2, DataType2..., {{Value1, Value2...},
{ValueN, ValueN+1...}...})