Here is a quick reference to DAX functions.

If you want to look at the functions in detail

please click here to go to Microsoft Dax reference.

  • Time and Date Functions
  • Time-Intelligence Functions
  • Filter Functions
  • Information Functions
  • Logical Functions
  • Math and Trig Functions
  • Other Functions
  • Text Functions
  • Parent and Child Functions
  • Statistical Functions
  • Aggregate Functions

Function

Description

CALENDAR (start date, end date)

New in Excel 2016/Power BI Desktop.

 

Returns a table with a single column named “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.

CALENDARAUTO ([end month of fiscal year])

New in Excel 2016/Power BI Desktop.

 

Returns a table with a single column named “Date” that contains a
contiguous set of dates. The range of dates is calculated automatically
based on data in the model.

DATE (year, month, day)

Returns the specified date in datetime format.

DATEDIFF (start date, end date, interval)

New in Excel 2016/Power BI Desktop.

Returns the count of interval boundaries crossed between two dates.

DATEVALUE (date text)

Returns the specified date in datetime format.

DAY (date)

Returns the day of the month, a number from 1 to 31.

EDATE (start date, months)

Returns a date in datetime format that is a number of months after the
start date.

EOMONTH (start date, months)

Returns a date in datetime format that is the last date in the month
after adding x months.

HOUR (datetime value)

Returns the hour as a number from 0 (12:00 A.M.) to 23 (11:00 P.M.).

MINUTE (datetime value)

Returns the minute as a number from 0 to 59, given a date and time
value.

MONTH (datetime value)

Returns the month as a number from 1 (January) to 12 (December).

NOW ()

Returns a date in datetime format.

SECOND (datetime value)

Returns the seconds of a time value, as a number from 0 to 59.

TIME (hour, minute, second)

Converts hours, minutes, and seconds given as numbers to a time in
datetime
format.

 

TIMEVALUE (time text)

Converts a time in text format to a time in datetime format.

 

TODAY ()

Returns the current date in datetime format.

Function

Notes

WEEKDAY (date, return type) 

Returns a number from 1 to 7 identifying the day of the week of a date.
By default, return type is 1, and the week begins on Sunday (1) and ends
on Saturday (7).

If return type is 2, the

week
begins on Monday (1) and ends on Sunday (7).

If return type is 3, the week begins on Monday (0) and ends on Sunday
(6).

 

WEEKNUM (date, return type)

Returns the week number for the given date and year according to the
return_type
value. The week number indicates where the week falls
numerically within a year.

If return type is 1, week begins on Sunday. Weekdays are numbered 1
through 7.

 

If return type is 2, week begins on Monday. Weekdays are numbered 1
through 7.

YEAR (datetime value)

Returns the year of a date as a four-digit integer in the range
1900-9999.

YEARFRAC (start_date,   
end_date, basis)

Calculates the fraction of the year represented by the number of whole
days between two dates. Use the YEARFRAC worksheet function to identify
the proportion of a whole year’s benefits or obligations to assign to a
specific term.


Function

Description

CLOSINGBALANCEMONTH

(expression, dates, [filter])

Evaluates the expression at the last date
of the month in the current context.

CLOSINGBALANCEQUARTER

(expression, dates, [filter])

Evaluates the expression at the last date
of the quarter in the current context.

CLOSINGBALANCEYEAR

(expression,
dates,
[filter],

[year_end_date])

Evaluates the expression at the last date
of the year in the current context.

DATEADD
(dates,

number_of_intervals, interval)

Returns a table that contains a column of dates, shifted either forward
or backward in time by the specified number of intervals from the dates
in the current context.

DATESBETWEEN (dates, start_date, end_date)


 

Returns a table that contains a column of dates that begins
with the start_date
and continues until the
end_date
.

DATESINPERIOD (dates, start_date, number_of_intervals, interval)

Returns a table that contains a column of dates that begins
with the start_date
and continues for the specified
number_of_intervals
.

DATESMTD (dates)

Returns a table that contains a column of the dates for the
month to date, in the current context.

DATESQTD (dates)

Returns a table that contains a column of the dates for the
quarter to date, in the current context.

DATESYTD (dates, [year_end_date])

Returns a table that contains a column of the dates for the
year to date, in the current context.

ENDOFMONTH (dates)

Returns the last date of the
month in the current context for the specified column of dates.

ENDOFQUARTER (dates)

Returns the last date of the quarter in the current context
for the specified column of dates.

ENDOFYEAR (dates, [year_end_date])

Returns the last date of the year in the current context for
the specified column of dates.

FIRSTDATE (dates)

 Returns the first
date in the current context for the specified column of dates.

FIRSTNONBLANK (column, expression)

 Returns the first
value in the column,
column
, filtered by the current context, where the
expression is not blank.

LASTDATE (dates)

 Returns the last date
in the current context for the specified column of dates.

LASTNONBLANK (column, expression)

 Returns the last
value in the column,
column
, filtered by the current context, where the
expression is not blank.

NEXTDAY (dates)

 Returns a table that
contains a column of all dates from the next day, based on the first
date specified in the
dates
column in the current context.

NEXTMONTH (dates)

 Returns a table that
contains a column of all dates from the next month, based on the first
date in the
dates
column in the current context.

NEXTQUARTER (dates)

 Returns a table that
contains a column of all dates in the next quarter, based on the first
date specified in the
dates
column, in the current context.

NEXTYEAR (dates, [year_end_date])

 Returns a table that
contains a column of all dates in the next year, based on the first date
in the dates
column, in the current context.

OPENINGBALANCEMONTH

(expression, dates, [filter])

Evaluates the expression at the first date of the month in
the current context.

OPENINGBALANCEQUARTER

(expression, dates, [filter])

Evaluates the expression at the first date of the quarter in
the current context.

OPENINGBALANCEYEAR

 
(expression,
dates,
[filter],

[year_end_date])

Evaluates the expression at the first date of the year in
the current context.

 
PARALLELPERIOD
(dates,

number_of_intervals, interval)

 

Returns a table that contains a column of dates that represents a period
parallel to the dates in the specified
dates
column, in the current context, with the dates

shifted a number of intervals either forward
in time or back in time.

PREVIOUSDAY (dates)

 

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.

PREVIOUSMONTH (dates)

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.

PREVIOUSQUARTER (dates)

Returns a table that contains a column of
all dates from the previous Quarter, based on the first date in the dates
column, in the current context.

PREVIOUSYEAR  (dates,

[year_end_date])

 

Returns a table that contains a column of all dates from the previous
quarter, based on the first date in the
dates
column, in the current context.

SAMEPERIODLASTYEAR (dates)

Returns a table that contains a column of
all dates from the previous year, given the last date in the dates
column, in the current context.

STARTOFMONTH (dates)

 

Returns the first date of the month in the current context for the
specified column of dates.

STARTOFQUARTER (dates)

 

Returns the first date of the quarter in the
current context for the specified column of dates.

STARTOFYEAR (dates)

 

Returns the first date of the year in the current context for the
specified column of dates.

TOTALMTD (expression, dates, [filter])

Evaluates the value of the expression for the month to
date, in the current context.

TOTALQTD (expression, dates, [filter])

Evaluates the value of the expression for the quarter
to date, in the current context.

TOTALYTD (expression, dates, [filter],
[year_end_date])  

Evaluates the value of thesub> expression for the year to
date, in the current context.

Function

Desctiption

ADDMISSINGITEMS(<showAllColumn>[,



<showAllColumn>]…,
<table>,

[ROLLUPISSUBTOTAL(]<groupingColumn>[,

<isSubtotal_columnName>][,

<groupingColumn>][,

<isSubtotal_columnName>]…[)], [, filterTable] …)

New in Excel 2016/Power BI Desktop.

 

Adds combinations of items from multiple columns to a table if they do
not already exist. The determination of which item combinations to add
is based on referencing source columns which contain all the possible
values for the columns.

To determine the combinations of items from different columns to
evaluate: AutoExist is applied for columns within the same table while
CrossJoin is applied across different tables.

The ADDMISSINGITEMS function will return BLANK values for the IsSubtotal
columns of blank rows it adds.

ALL (TableOrColumn, [TableOrColumn], …)

Returns the table or column with filters removed.

ALLEXCEPT (table, column, [column], …)

Returns the table with all filters removed except for the filters on the
specified columns.

ALLNOBLANKROW (table | column)

Returns a table, when the passed parameter was a table, or a column of
values, when the passed parameter was a column.

ALLSELECTED ([tableName | columnName])

Keeps filters on Rows and Columns in a pivot table while keeping the
filters on slicers and other explicit filters.

CALCULATE (expression, [filter1], [filter2], …)

Modifies the filter context prior to calculating the expression.

CALCULATETABLE (expression, filter1, filter2,

…)

Modifies the filter context prior to returning a table of values.

CROSSFILTER (columnName1, columnName2, direction)

Allows you to change the filtering direction for a single measure.
 Place the CROSSFILTER inside a
CALCULATE

DISTINCT (column)  

Returns a 1 column table of all the distinct values in the current
filter context.  If there
are BLANKS then they will be ignored.
If you want to return a BLANK as well, then use VALUES instead.

EARLIER (column, [number])

Used to access a previous row context when more than 1 row context
exists in the function.

EARLIEST (column)  

As above, but returns the absolute first row context.

FILTER (table, filter)

Returns a table containing only the filtered rows.

FILTERS (columnName)  

Returns a table containing the list of values that are directly
filtered.

HASONEFILTER (columnName)

Used to check if there is one and only one filter on a column in the
current filter context.

HASONEVALUE (columnName)

Used to check if there is one and only one value visible in a column in
the current filter context.

SELECTEDVALUE(ColumnName)

This is new in Power BI Desktop.
It can be used as a substitute for IF(HASONEVALUE()) and will
return a scalar value if there is one and only 1 value selected in the
current filter context.

ISCROSSFILTERED (columnName)

Used to check if there is an indirect filter on a column in the current
filter context.

ISFILTERED (columnName)

Used to check if there the column is filtered at all in the current
filter context.

KEEPFILTERS (expression)

CALCULATE will replace a filter on the same column.
If you use KEEPFILTERS, it will add the new filter to the column
rather than replace it.

RELATED (column)  

Forces a row context to follow the relationship to a related table and
return that value.  Can only
be used on the many side of the relationship.

RELATEDTABLE (tableName)

Forces a row context to follow the relationship to a related table and
return that value.  Can only
be used on the one side of the relationship.
Returns a table of values from the many side of the relationship.

SUBSTITUTEWITHINDEX (table, indexColumnName, indexColumnsTable,

orderBy_expression, [order],
[orderBy_expression, [order]] …)

New in Excel 2016/Power BI Desktop

Returns a table which represents a left semijoin of the two tables
supplied as arguments. The semijoin is performed by using common
columns, 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.

Columns in the right/second table supplied which do not exist in the
left/first table supplied are not included in the returned table and are
not used to join on.

The index starts at 0 (0-based) and is incremented by one for each
additional row in the right/second join table supplied. The index is
based on the sort order specified for the right/second join table.

The index starts at 0 (0-based) and is incremented by one for each
additional row in the right/second join table supplied. The index is
based on the sort order specified for the right/second join table.

 

USERELATIONSHIP (columnName1,
columnName2)  

You can have more than 1 relationship between 2 tables in DAX, but only
1 can be active at a time. Use this function inside CALCULATE to use the
inactive relationship instead of the active one.

VALUES (TableNameOrColumnName)

Returns a table consisting of a single column of unique values in the
current filter context.  If
there are blanks in the list a blank will be returned.
If you want to exclude the blank then use DISTINCT instead.

VALUES (TableNameOrColumnName)

Returns a table consisting of a single column of unique values in the
current filter context.  If
there are blanks in the list a blank will be returned.
If you want to exclude the blank then use DISTINCT instead.

Function

Description

CONTAINS (table, columnName, value,

[columnName, value] …)

Returns TRUE if each
specified value is contained in the corresponding
columnName.

Otherwise, the function returns FALSE.

CUSTOMDATA () 

Returns the content of the
CustomData property in the connection string.

Returns Blank, if
CustomData property was not defined at connection time.

ISBLANK (value)

Returns True if the value is blank.

Otherwise, returns FALSE.

ISEMPTY (table_expression) 

 

 

New in Excel 2016/Power BI Desktop.

Returns True if the table is empty (has no rows),
Returns False otherwise.

ISERROR (value)

Returns True if the value is an Error.

Otherwise, returns FALSE.

ISEVEN (number) 

Returns TRUE if number is even, Returns FALSE if number is odd.

If number is nonnumeric, ISEVEN returns the #VALUE! error value.

ISLOGICAL (value)

Returns TRUE if the value is a logical value (TRUE OR FALSE).

Otherwise, returns FALSE.

ISNONTEXT (value)  

Returns TRUE if the value is not text or blank Returns FALSE if the
value is text.

An empty string is considered text.

ISNUMBER (value)  

Returns TRUE if the value is numeric.

Otherwise, returns FALSE.

ISODD (number)

Returns TRUE if number is odd.

Returns FALSE if number is even.

If number is nonnumeric, ISODD returns the #VALUE! error value.

ISONORAFTER (scalar expression, scalar expression, [sort order], [scalar
expression, scalar expression, [sort order]], …)

New in Excel 2016/Power BI Desktop.

This function takes a variable number of triples, the first two
parameters in a triple are the expressions to be compared

and the third is the sort order – ascending (default) or descending.

Based on the sort order, the first parameter is compared with the second
parameter. 

If the sort order is ascending, the comparison to be done is first
parameter greater than or equal to second parameter.

If the sort order is descending, the comparison to be done is first
parameter less than or equal to second parameter

ISTEXT (value)  

Returns TRUE if the value is text Otherwise, returns FALSE.

Empty string is text.

Blank is not text.

LOOKUPVALUE ( result_columnName, search_columnName, search_value,

[search_columnName, search_value],

)

The value of result_column at the row where all pairs of search_column
and search_value have a match.

If only some of the criteria match, a BLANK is returned.

If multiple rows match the search values and in all cases
result_column values are identical, then that value is
returned. Otherwise, an error is returned.

USERNAME ()

Returns the username from the credentials given to the system at
connection time.

Function

Description

AND (logical_value, logical_value)

Checks whether both arguments are TRUE, and returns TRUE if both
arguments are TRUE. Otherwise returns false.

  If you have more than two
arguments, use && (double ampersand) as an alternative and you can have
as many “and” as you like.

FALSE ()

Returns the logical value FALSE.

IF      
(logical
test,
value_if_true,

[value_if_false])

Checks if a condition provided as the first argument is met. Returns one
value if the condition is TRUE, and returns another value if the
condition is FALSE.

Returns blank, if the condition is FALSE and value_if_false is omitted.

IFERROR (value, value_if_error)

Evaluates an expression and returns a specified value if the expression
returns an error. 
Otherwise, returns the value of the expression itself.

NOT (logical_value)

Changes FALSE to TRUE, or TRUE to FALSE.

OR (logical_value, logical_value)

Checks whether one of the arguments is TRUE to return TRUE. The function
returns FALSE if both arguments are FALSE.

If you have more than two arguments, use use || (double pipe) as an
alternative and you can have as many “or” as you like.

SWITCH (Expression, value1, expression1,
[value2, expression2], ….

[else, expression])

Expression is evaluated and the result is matched with the given values.
If a match is found, the corresponding expression is evaluated.

If the result is not matched with any of the given values, and else is
given, the corresponding expression is evaluated.

All expressions must be of the same data type.

TRUE ()

Returns the logical value TRUE.


Function

Description

ABS (number)

Removes the negative sign if
it exists.

ACOS (number)

New in Excel 2016/Power BI Desktop.

Returns the arccosine, or inverse cosine, of a number. The arccosine is
the angle whose cosine is
number
. The returned angle is given in radians in the
range 0 (zero) to pi.

ACOSH (number)

New in Excel 2016/Power BI Desktop.

Returns the inverse hyperbolic cosine of a 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.

ASIN (number)

New in Excel 2016/Power BI Desktop.

Returns the arcsine, or inverse sine, of a 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.

ASINH (number)

New in Excel 2016/Power BI Desktop.

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

ATAN (number)

New in Excel 2016/Power BI Desktop.

Returns the arctangent, or inverse tangent, of a number.

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

ATANH (number)

New in Excel 2016/Power BI Desktop.

Returns the inverse
hyperbolic tangent of a 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.

CEILING (number, significance)

 

Rounds a number up, to the nearest integer or to the nearest
multiple of significance.

COMBIN (number, number_chosen)

New in Excel 2016/Power BI Desktop.

Returns the number of
combinations for a given number of items.

Numeric arguments are truncated to integers.

If either argument is nonnumeric, COMBIN returns the #VALUE!
error value.

If number< 0, number_chosen< 0, or number< number_chosen,
COMBIN returns the #NUM! error value.

COMBINA (number, number_chosen)

New in Excel 2016/Power BI Desktop.

Returns the number of
combinations (with repetitions) for a given number of items.

<![if !supportLists]>


<![endif]>number must be greater than or equal to
0, and greater than or equal to number_chosen.

<![if !supportLists]>


<![endif]>number_chosen must be greater than or
equal to 0.  

If the value of either
argument is outside of it’s constraints, COMBINA returns the #NUM! error
value.

If either argument is a
non-numeric value, COMBINA returns the #VALUE! error value.

Non-integer values are truncated.

COS (number)

New in Excel 2016/Power BI Desktop.

Returns the cosine of the given angle.

COSH (number)

New in Excel 2016/Power BI Desktop.

Returns the hyperbolic cosine of a number.

CURRENCY (value)

The
value of the expression evaluated and returned as a currency type value.

DEGREES (angle)

New in Excel 2016/Power BI Desktop.

Converts radians into degrees.

 
DIVIDE
(numerator,

[alternate-result]).

denominator,

Safe divide function
that gracefully handles a divide by zero error.

 

Performs division and returns alternate result or BLANK() on division by
0.

EVEN (number)

New in Excel 2016/Power BI Desktop.

Returns number rounded up to the nearest
even integer.

EXP (number)

New in Excel 2016/Power BI Desktop.

Returns e raised to the power of a given number. The constant e equals
2.71828182845904, the base of the natural logarithm.

FACT (number)

Returns the factorial of a number, equal to the series 1*2*3*…* ,
ending in the given number.

FLOOR (number, significance)

Rounds a number down,
toward zero, to the nearest multiple of significance.

 

 

GCD (number1, [number2], …)

New in Excel 2016/Power BI Desktop.

Returns the greatest common divisor of two or more integers. The
greatest common divisor is the largest integer that divides both number1
and number2 without a remainder.

INT (number)

Rounds a number down to the
nearest integer.

ISO.CEILING (number, [significance])

 

New in Excel 2016/Power BI Desktop.

Rounds number up, to the nearest multiple of significance.

Rounds number up, to the
nearest integer if significance is omitted.

LCM (number1, [number2], …)

New in Excel 2016/Power BI Desktop.

 

Returns the least common multiple of integers. The least common multiple
is the smallest positive integer that is a multiple of all integer
arguments number1, number2, and so on.
 

LN (number)

Natural Log

LOG (number, base)

You might receive an error
if the value is too large to be displayed.

LOG10 (number)

Returns the base-10
logarithm of a number.

MOD (number, divisor)

If the divisor is 0 (zero), MOD returns an error. You cannot
divide by 0.

MROUND (number, multiple)

New in Excel 2016/Power BI Desktop.

 

Returns a number rounded to
the desired multiple.

ODD (number)

New in Excel 2016/Power BI Desktop.

Returns number rounded up to
the nearest odd integer.

PERMUT (number, number_chosen)

Returns the number of
permutations for a given number of objects that can be selected from
number objects. 

A permutation is any set or subset of objects or events
where internal order is significant.

PI ()

New in Excel 2016/Power BI
Desktop.

Returns the value of Pi, 3.14159265358979, accurate to 15
digits.

POWER (number, power)

Returns the result of a number raised to a power.

 

 

QUOTIENT (numerator, denominator)

New in Excel 2016/Power BI Desktop.

 

Performs division and
returns only the integer portion of the division result. Use this
function when you want to discard the remainder of division.

RADIANS (angle)

New in Excel 2016/Power BI
Desktop.

Converts degrees to radians.

RAND ()

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

RANDBETWEEN (bottom, top)

Returns a random number in the range between two numbers you specify.

ROUND (number, num_digits)

Rounds a number to the specified number of digits.

ROUNDDOWN (number, num_digits)

Rounds a number down, toward
zero.

ROUNDUP (number, num_digits)

Rounds a number up, away
from 0 (zero).

SIGN (number)

Determines the sign of a
number, the result of a 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.

SIN (number)

New in Excel 2016/Power BI Desktop.

Returns the sine of the given angle.

SINH (number)

New in Excel 2016/Power BI Desktop.

Returns the hyperbolic sine
of a number.

SQRT (number)

Returns the square root of a number.

If the number is negative,
the SQRT function returns an error.

SQRTPI(number)

 

New in Excel 2016/Power BI Desktop.

Returns the square root of (number * pi).

TAN (number)

New in Excel 2016/Power BI Desktop.

Returns the tangent of the
given angle.

TANH (number)

New in Excel 2016/Power BI Desktop.

Returns the hyperbolic
tangent of a number.

TRUNC (number, num_digits)

Truncates a number to an
integer by removing the decimal, or fractional, part of the number.
num_digits specifies the precision of the truncation; if omitted,
0 (zero).


Function

Description

DATATABLE
(ColumnName1,

DataType1,

ColumnName2, DataType2 …,

{{Value1, Value2 …},

  {Value1, Value2
…}, 

  

  {Value1, Value2
…}

}

)

New in Excel 2016/Power BI Desktop

Returns a table declaring an inline set of values.

Each of the columns is
given a name and the data type of the column is provided.

Then, the set of values is given –

{{Row 1 values},

  {Row 2 values},

   

}

EXCEPT
(table_expression1,

table_expression2)

Returns a table that contains the rows of one table minus
all the rows of another table.

GROUPBY
(table,

[groupBy_columnName1],
[name,

expression], …)

 

New in Excel 2016/Power BI
Desktop.    
 

The GROUPBY function
is similar to the SUMMARIZE 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 making it highly performant.

The expression used in GroupBy may include any of the “X”
aggregation functions.

INTERSECT
(table_expression1,

table_expression2)

New in Excel 2016/Power BI Desktop.

 

Returns the row intersection
of two tables, retaining duplicates.

NATURALINNERJOIN    
(leftJoinTable,

rightJoinTable)

 

New in Excel 2016/Power BI Desktop.

 

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

NATURALLEFTOUTERJOIN

(leftJoinTable, rightJoinTable)

 

New in Excel 2016/Power BI Desktop.

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

 

UNION (table_expression1, table_expression2)

 

New in Excel 2016/Power BI Desktop.

Creates a union (join) table from a pair of tables.

Returns a table that
contains all the rows from each of the two table expressions.

The two tables must have the same number of columns.

Columns are combined by
position in their respective tables.

The column names in the
return table will match the column names in table_expression1.

Duplicate rows are retained.

The returned table
has lineage where possible. When data types differ, the resulting data
type is determined based on the rules for data type coercion.

The returned table will not
contain columns from related tables.

GENERATE (table1, table2)

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.

GENERATEALL (table1, table2)

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.

GENERATESERIES (StartValue,

EndValue, [IncrementValue])

Generates a table of values using the parameters provided

VAR VarName = Expression

 

New in Excel 2016/Power BI Desktop.

Stores the result of
an expression as a named variable, which can then be passed as an
argument to other measure expressions. Once resultant values have been
calculated for a variable expression, those values do not change, even
if the variable is referenced in another expression.

VarName is the name of the variable (identifier).

<![if !supportLists]>


<![endif]>Supported character set: a-z, A-Z, 0-9.

<![if !supportLists]>


<![endif]>0-9 are not valid as first character.

<![if !supportLists]>


<![endif]>__ (double underscore) is allowed as a
prefix. No other special characters are supported.

Delimiters are not supported. For example, ‘VarName’ or
[VarName] will result in an error.

<![if !supportLists]>


<![endif]>Reserved keywords not allowed.

<![if !supportLists]>


<![endif]>Names of existing tables are not allowed.

<![if !supportLists]>


<![endif]>Empty spaces are not allowed. 

Expression is a DAX
expression which returns a scalar or table value.

<![if !supportLists]>


<![endif]>Expression can contain another VAR
declaration.

When referencing a variable:

<![if !supportLists]>


<![endif]>Measures cannot refer to variables
defined outside the measure expression, but can refer to functional
scope variables defined within the expression.

<![if !supportLists]>


<![endif]>Variables can refer to measures.

<![if !supportLists]>


<![endif]>Variables can refer to previously defined
variables.

Columns in table variables
cannot be referenced via TableName[ColumnName] syntax.

AVERAGEX (table, expression)

Calculates the average
(arithmetic mean) of a set of expressions evaluated over a table.

CONCATENATEX (table, expression,

[delimiter])

New in Excel 2016/Power BI Desktop.

 

Concatenates the result of an expression evaluated for each
row in a table.

COUNTAX (table, expression)

The COUNTAX function
counts nonblank results 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 result in a nonblank result.

COUNTX (table, expression)

Counts the number of rows that contain a number or an expression that
evaluates to a number, when evaluating an expression over a table.

GEOMEANX (table, expression)

New in Excel 2016/Power BI Desktop.

Returns the geometric mean of an expression evaluated for each row in a
table.

MAXX (table, expression)

Evaluates an expression for each row of a table and returns
the largest numeric value.

MEDIANX (table, expression)

New in Excel 2016/Power BI Desktop.

Returns the median of an
expression evaluated for each row in a table.

MINX (table,
expression) 

 

Returns the smallest numeric
value that results from evaluating an expression for each row of a
table.

PERCENTILEX.EXC
(table, 

expression, k)

New in Excel 2016/Power BI Desktop.

Returns the percentile number of an expression evaluated for
each row in a table.

PERCENTILEX.INC
(table, 

expression, k)

New in Excel 2016/Power BI Desktop.

Returns the percentile number of an expression evaluated for
each row in a table.

PRODUCTX (table, expression)

New in Excel 2016/Power BI Desktop.

Returns the product of an expression evaluated for each row
in a table.

RANKX (table, expression, [value],

[order], [ties])

Returns the ranking of a number in a list of numbers for
each row in the table argument.

STDEVX.P (table, expression)

expression is any DAX expression that returns a single
scalar value, where the expression is to be evaluated multiple times
(for each row/context).

SUMX (table, expression)

Returns the sum of an expression evaluated for each row in a
table.

VARX.P (table, expression)

Returns the variance of the entire population.

VARX.S (table, expression)

 

Returns the variance of a sample population.

 

 

XIRR (table, values, dates, [guess])

New in Excel 2016/Power BI Desktop.

Returns the internal rate of
return for a schedule of cash flows that is not necessarily periodic.

XNPV (table, values, dates, rate)


 

New in Excel 2016/Power BI Desktop.

Returns the present value
for a schedule of cash flows that is not necessarily periodic.

IGNORE (expression)

IGNORE function does not return a value.

IGNORE can be used as an expression argument to
SUMMARIZECOLUMNS function.

ROLLUPADDISSUBTOTAL ( groupBy_columnName,
isSubtotal_columnName,

[groupBy_columnName,

isSubtotal_columnName] …

)

ROLLUPADDISSUBTOTAL function does not
return a value. It only specifies the set of columns to be subtotalled.

ROLLUPADDISSUBTOTAL can be used with SUMMARIZECOLUMNS
function.

ROLLUPGROUP
(groupBy_columnName, groupBy_columnName)

ROLLUPGROUP
    can
     only
    be

used   as

a groupBy_columnName
argument
to
the ROLLUPADDISSUBTOTAL and / or the SUMMARIZE functions.

Function

Description

BLANK ()

Returns a blank.

Blanks are not equivalent to nulls. DAX uses blanks for both database
nulls and for blank cells in Excel.
 

CODE (text)

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

CONCATENATE (text1, text2) 


 

Joins two text strings into one text string.

If you need to add more than two arguments, use the AND (&) operator.

EXACT (text1, text2) 

 

Compares two text strings and returns TRUE if they are exactly the same,
FALSE otherwise. EXACT is casesensitive but ignores formatting
differences.
 

FIND
(find_text,
within_text,

[start_num], [NotFoundValue])

Returns the starting position of one text string within another text
string. FIND is case-sensitive.

FIXED
(number,
decimals,

no_commas)  

Rounds a number to the specified number of decimals and returns the
result as text. You can specify that the result be returned with or
without commas.

FORMAT (value, format_string)

Converts a value to text according to the specified format.

LEFT (text, num_chars)

Returns the specified number of characters from the start of a text
string.

LEN (text)

Returns the number of characters in a text string.

LOWER (text) 

Converts all letters in a text string to lowercase.

MID (text, start_num, num_chars)

Returns a string of characters from the middle of a text string, given a
starting position and length.

REPLACE    
(old_text,
start_num, num_chars, new_text)

REPLACE replaces part of a text string, based on the number of
characters you specify, with a different text string.

REPT (text, num_times)

Repeats text a given number of times. Use REPT to fill a cell with a
number of instances of a text string.

RIGHT (text, num_chars)

RIGHT returns the last character or characters in a text string, based
on the number of characters you specify.

SEARCH (find_text, within_text, [start_num], [NotFoundValue])

Returns the number of the character at which a specific character or
text string is first found, reading left to right.
 

Search function is case insensitive and accent sensitive.

SUBSTITUTE (text, old_text, new_text, instance_num)

Replaces existing text with new text in a text string.

TRIM (text)

Removes all spaces from text except for single spaces between words.

UPPER (text)

Converts a text string to all uppercase letters

VALUE (text)

You need not use the VALUE function in a formula because Power Pivot
implicitly converts text to numbers.

The argument text can be a constant or in one of the formats – number,
date or time format. Otherwise, an error is returned.
 

You can use a column reference as argument to VALUE function. E.g., if
you have a column that contains mixed number types, VALUE can be used to
convert all values to a single numeric data type. However, if you use
the VALUE function with a column that contains mixed numbers and text,
the entire column is flagged with an error, because not all values in
all rows can be converted to numbers.

Function

Description

PATH (ID_columnName, 
parent_columnName)

Returns a delimited text string with the identifiers of all the parents
of the current identifier, starting with the oldest and continuing until
current.

PATHCONTAINS (path, item)

Returns
TRUE
if

the
specified
item
exists within the
specified
path
.

PATHITEM (path, position, [type])

Returns the item at the specified
position
from a
string resulting from evaluation of a PATH function. Positions are
counted from left to right.

PATHITEMREVERSE (path, position,

[type])

Returns the item at the specified
position
from a
string resulting from evaluation of a PATH function. Positions are
counted backwards from right to left.

PATHLENGTH (path)  

Returns the number of parents to the specified item in a given PATH
result, including self.

Function

Description

BETA.DIST
(x,
alpha,
beta,

cumulative, [A], [B])

New in Excel 2016/Power BI Desktop.

Returns the beta distribution. The beta distribution is commonly used to
study variation in the percentage of something across samples, such as
the fraction of the day people spend watching television.

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

New in Excel 2016/Power BI Desktop.

Returns the inverse of the beta cumulative probability density function
(BETA.DIST).

If
probability
=
BETA.DIST(x,…TRUE),
then

BETA.INV(probability,…) = x.

CHISQ.INV deg_freedom)

(probability,

New in Excel 2016/Power BI Desktop.

Returns the inverse of the left-tailed probability of the chisquared
distribution.

The chi-squared distribution is commonly used to study variation in the
percentage of something across samples.

CHISQ.INV.RT deg_freedom)

(probability,

New in Excel 2016/Power BI Desktop.

Returns the inverse of the right-tailed probability of the chisquared
distribution.

If
probability
=
CHISQ.DIST.RT(x,…),
then

CHISQ.INV.RT(probability,…) = x. Use this function to compare observed
results with expected ones in order to decide whether your original
hypothesis is valid.

CONFIDENCE.NORM

standard_dev, size)

(alpha,

New in Excel 2016/Power BI Desktop.

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.
 

CONFIDENCE.T

standard_dev, size)

(alpha,

New in Excel 2016/Power BI Desktop.

Returns the confidence interval for a population mean, using a Student’s
t distribution.

EXPON.DIST
(x,

cumulative)

lambda,

New in Excel 2016/Power BI Desktop.

Returns the exponential distribution. Use EXPON.DIST to model the time
between events.

GEOMEAN (column)

New in Excel 2016/Power BI Desktop.

Returns the geometric mean of the numbers in a column.

MEDIAN (column)

 

New in Excel 2016/Power BI Desktop.

Returns the median of numbers in a column.

PERCENTILE.EXC (column, k)

New in Excel 2016/Power BI Desktop.

Returns the k-th percentile of values in a range, where k is in the
range 0..1, exclusive.

PERCENTILE.INC (column, k)

New in Excel 2016/Power BI Desktop.

Returns the k-th percentile of values in a range, where k is in the
range 0..1, inclusive.

POISSON.DIST
(x,
mean,

cumulative)

Returns the Poisson distribution.

A common application of the Poisson distribution is predicting the
number of events over a specific time.

RANK.EQ
(value,
columnName,

[order])

Returns the ranking of a number in a list of numbers.

SAMPLE (n_value, table,

[orderBy_expression], [order],

[orderBy_expression], [order],

)

Returns a sample of N rows from the specified table.

STDEV.P (ColumnName)

 

Returns the standard deviation of the entire population.

STDEV.S (ColumnName)

Returns the standard deviation of a sample population.

VAR.P (columnName)

Returns the variance of the entire population.

VAR.S (columnName)

Returns the variance of a sample population.

Function

Description

AVERAGE (column)

Returns the average (arithmetic mean) of all the numbers in
a column in the current filter context.

AVERAGEA (column)

The AVERAGEA
function takes a column and averages the numbers in it, but also handles
non-numeric data types according to the following rules:

Values that evaluates to TRUE count as 1.

Values that evaluate to FALSE count as 0 (zero).

Values that contain non-numeric text count as 0 (zero).

Empty text (“”) counts as 0
(zero).

COUNT (column)

Counts numbers only in the
current filter context.

COUNTA (column)

Counts text values as well
as numbers in the current filter context.

COUNTBLANK (column)

Counts the number of blank
cells in a column in the current filter context.

COUNTROWS (table)

The COUNTROWS function counts the number of rows in the specified table,
or in a table defined by an expression in the current filter context.

DISTINCTCOUNT (column)

Counts each value in a column once and only once
in the current filter context
.

MAX (column)

Returns the largest numeric
value in a column in the current filter context.

MAXA (column)

Like MAX, however also considers Dates and Logical values, such as TRUE
and FALSE. Rows that evaluate to TRUE count as 1; rows that evaluate to
FALSE count as 0 (zero)

MIN (column)

Returns the smallest numeric
value in a column. Ignores logical values and text in the current filter
context.

MINA (column)

Like MIN, however also
considers Dates and Logical Values.

PRODUCT (column)

New in Excel 2016/Power BI Desktop.

Multiplies all the values in a column together.
Why you would want to? I have no idea.

SUM (column)

Adds all the numbers in a
column in the current filter context.

TOPN (n_value, table, orderBy_expression, [order], [orderBy_expression,
[order]] …)

Returns a table containing the top N rows.
Order by expression is typically a measure that you want to rank
on.

Start Learning Now …

Business Intelligence course $9.99
© Biztics.com. All rights reserved.
X