PowerBI DAX CHEAT SHEET

FUNCTION NAME DESCRIPTION SYNTAX PARAMETERS

Returns all rows from a table or all

table: The table to remove filters from.
ALL values from a column, ignoring any ALL(<table> [, <column>]...)
column: The column to remove filters from.
filters that may have been applied.

Removes all context filters in the table

ALLEXCEPT(<table>, table: The table to remove filters from.
ALLEXCEPT except those that are explicitly <column1>, <column2>,...) column: Columns to keep filters applied to.
specified in the argument.

Removes filters from the columns

ALLSELECTED(<table> [, table: Table from which to remove filters.
ALLSELECTED and rows of the current query but <column>]...) column: Column to remove filters from.
keeps filters from other contexts.

CALCULATE(<expression>, expression: Expression to evaluate.

Evaluates an expression in a
CALCULATE modified filter context. <filter1>, <filter2>...) filter: Boolean expression or table defining filter
conditions.

expression: Table expression to evaluate.

Evaluates a table expression in a CALCULATETABLE(<expressio
CALCULATETABLE modified filter context. n>, <filter1>, <filter2>...)
filter: Boolean expressions defining filter
conditions.

Specifies the cross-filtering direction column1, column2: Columns between which the
CROSSFILTER(<column1>,
CROSSFILTER to be used in a calculation for
<column2>, <direction>)
cross-filtering is applied.
relationships between two columns. direction: Filter direction.

Returns a one-column table that

DISTINCT contains the distinct values from the DISTINCT(<column>) column: Column to return unique values from.
specified column.

RAJAT SAXENA 01
PowerBI DAX CHEAT SHEET
RETURNS A TABLE WITH A SUBSET COLUMN: THE COLUMN FOR WHICH TO
FUNCFTIILOTNE RNAME OF RODWES STHCARTI PMTEEITO FILTERS(<COLUMN>)
AN GIVEN
SYNTAX RETURPNA TRHAE MFILETTEER RVAS
CONDITION.

Returns TRUE if a filter has been

ISFILTERED applied directly to the specified ISFILTERED(<column>) column: Column to check for a direct filter.
column.

Returns a related value from another columnName: The column for which to return a
RELATED table.
RELATED(<columnName>)
related value.

Returns a table related to the current

RELATEDTABLE one in a modified filter context.
RELATEDTABLE(<table>) table: The related table to return.

UNION(<table_expression>, table_expression: Table expressions to combine

Creates a union of two or more
UNION <table_expression>,...) into a single table.
tables.

dates: A column containing dates.

Returns a table containing a column
number_of_intervals: The number of intervals to
of dates shifted forward or CALCULATETABLE(<expression
DATEADD backward by a specified number of >, <filter1>, <filter2>...)
add or subtract.
interval: The interval by which to shift the dates
intervals.
(year, quarter, month, day).
dates: A column containing dates.
Returns a table containing the dates
DATESYTD(<dates> [, year_end_date: (Optional) The end of the year
DATESYTD for the year up to the current date in
<year_end_date>]) date.
the current context.
Default is December 31.

Evaluates the expression at the last expression: The expression to evaluate.

CLOSINGBALANCEMONTH(<ex
CLOSINGBALANCEMONTH date of the month in the current
pression>, <dates> [, <filter>]) dates: A column containing dates.
context. filter: (Optional) A filter expression.

RAJAT SAXENA 02
PowerBI DAX CHEAT SHEET
EVALUATES THE EXPRESSION FOR TOTALYTD(<EXPRESSION>, EXPRESSION: THE EXPRESSION TO EVALUATE.
FUNTCOTIAOLNY NTADMETHE DYEASRC TROI DPATTIEO IN
<DATES>Y
THE [N, <TFAILXTER>]
DATES:
[, A CPOALURMANM CEOTNTEAR
CURRENT CONTEXT. <YEAR_END_DATE>]) FILTER

Returns the last date in the current

LASTDATE context for the column of dates.
LASTDATE(<dates>) dates: A column containing dates.

Returns the first date of the month in

STARTOFMONTH the current context for the specified STARTOFMONTH(<dates>) dates: A column containing dates.
dates.

Returns a table containing a column

PREVIOUSMONTH of dates from the previous month in PREVIOUSMONTH(<dates>) dates: A column containing dates.
the current context.

Returns a table containing a column

NEXTMONTH(<dates>) dates: A column containing dates.
NEXTMONTH of dates from the next month in the
current context.

Returns a table with a column of PARALLELPERIOD(<dates>, dates: A column containing dates.

PARALLELPERIOD dates shifted forward or backward by <number_of_intervals>, number_of_intervals: The number of intervals to
the specified number of intervals. <interval>) shift.

Returns a table containing the dates dates: A column containing dates.

DATESYTD(<dates> [,
DATESYTD for the year up to the current date in year_end_date: (Optional) The end of the year
<year_end_date>]) date.
the current context.
Default is December 31.

Evaluates the expression at the last expression: The expression to evaluate.

CLOSINGBALANCEMONTH(<ex
CLOSINGBALANCEMONTH date of the month in the current
pression>, <dates> [, <filter>]) dates: A column containing dates.
context. filter: (Optional) A filter expression.

RAJAT SAXENA 03
PowerBI DAX CHEAT SHEET
EVALUATES THE EXPRESSION FOR TOTALYTD(<EXPRESSION>, EXPRESSION: THE EXPRESSION TO EVALUATE.
FUNTCOTIAOLNY NTADMETHE DYEASRC TROI DPATTIEO IN
<DATES>Y
THE [N, <TFAILXTER>]
DATES:
[, A CPOALURMANM CEOTNTEAR
CURRENT CONTEXT. <YEAR_END_DATE>]) FILTER

Returns the number: The

ABS absolute value of a number. ABS(<number>) number for which you want the absolute value.
numerator: The dividend or number to divide.

Performs DIVIDE(<numerator>, denominator:

DIVIDE division and returns an alternate result <denominator> [, The divisor or number by which the numerator is
or BLANK() if division by 0 occurs. <alternateresult>]) divided.

Returns column:
PRODUCT the product of numbers in a column. PRODUCT(<column>) The column containing the numbers to calculate
the product of.

Returns table:
the product of an expression PRODUCTX(<table>, The table containing rows for which the
PRODUCTX <expression>)
evaluated for each row in a table. expression is evaluated.

number:
Rounds
ROUND(<number>, The number to round.
ROUND a number to the specified number of
<num_digits>) num_digits: The number of digits to round the
digits.
number to.

Adds column:
SUM all the numbers in a column.
SUM(<column>)
The column containing the numbers to sum.

Returns table:
SUMX(<table>,
SUMX the sum of an expression evaluated
<expression>)
The table containing rows for which the
for each row in a table. expression is evaluated.

RAJAT SAXENA 04
PowerBI DAX CHEAT SHEET

FUNCTION NAME DESCRIPTION SYNTAX PARAMETERS

table: Table to be summarized.

SUMMARIZE(<table>,
Returns a summary table for the groupBy_columnName: The column by which to group
SUMMARIZE requested data.
<groupBy_columnName>, the data.
[<name>, <expression>]...) expression: The aggregation to perform.

Returns a summary table for the requested SUMMARIZECOLUMNS(<groupBy_ groupBy_columnName: The column by which to
data, similar to SUMMARIZE, but with
SUMMARIZECOLUMNS added functionality.
columnName>, <filterName>, group.
<filterExpression>,...) filterExpression: Optional filter expression.

expression: The measure or column to calculate.

TOTALQTD(<expression>,
TOTALQTD Calculates the quarter-to-date value.
<dates>, [,<filter>]) dates: The date column.
filter: Optional filter.

start_date: Starting date.

Returns the difference between two DATEDIFF(<start_date>,
DATEDIFF end_date: Ending date.
dates. <end_date>, <interval>)
interval: Time interval (e.g., day, month, year).

date: The date column.

Returns the name of a given date DATENAME(<date>, interval: The date component (year, month, day,
DATENAME component, such as year, month, day. <interval>) etc.).

Applies the result of a table TREATAS(<table>, <column1>, table: The table to be treated as a filter.
TREATAS column: The column(s) on which to apply the filter.
expression as filters on columns. <column2>,...)

Ignores all relationships between NORELATIONSHIPS(<table>, table: The table to ignore relationships for.
NORELATIONSHIPS tables during a query. <column>, ...) column: The columns to apply this behavior to.

RAJAT SAXENA 05
PowerBI DAX CHEAT SHEET

FUNCTION NAME DESCRIPTION SYNTAX PARAMETERS

Performs an inner join between two NATURALINNERJOIN(<table1>, table1: The first table.
NATURALINNERJOIN tables. <table2>) table2: The second table to join with.

Performs a left outer join between two NATURALLEFTOUTERJOIN(<table1 table1: The first table.
NATURALLEFTOUTERJOIN tables. >, <table2>) table2: The second table to join with.

EVALUATE Executes a table expression. EVALUATE <expression> expression: The expression to evaluate.

value: The value or expression to check.

Returns an alternate value if an error IFERROR(<value>,
IFERROR <alternateValue>)
alternateValue: The value to return if there is an
occurs. error.

ISBLANK Checks if a value is blank. ISBLANK(<value>) value: The value to check.

ISNUMBER Checks if a value is a number. ISNUMBER(<value>) value: The value to check.

ISTEXT Checks if a value is text. ISTEXT(<value>) value: The value to check.

RAJAT SAXENA 06
PowerBI DAX CHEAT SHEET

FUNCTION NAME DESCRIPTION SYNTAX PARAMETERS

lookup_value: The value to search for.

MATCH(<lookup_value>,
Returns the position of a value in a lookup_array: The range to search.
MATCH list.
<lookup_array>, match_type: Optional type.
[<match_type>]) table: The table or range.

table: The table or range.

Returns the value of a cell in a range, INDEX(<table>, <row_num>,
INDEX based on row and column numbers. <column_num>)
row_num: The row number.
column_num: The column number.

Returns the maximum value in a column: The column containing the values to
MAX column.
MAX(<column>)
check.

Returns the maximum value, but it column: The column containing the values to
MAXA MAXA(<column>)
includes logical values. check.

MOVINGAVERAGE(<expression expression: The value to calculate.

MOVINGAVERAGE Calculates a moving average. >, <dates>, dates: The date range.
<number_of_intervals>) number_of_intervals: The number of periods.

Returns the end of the month for a EOMONTH(<start_date>, start_date: The start date.
EOMONTH given date. <months>) months: The number of months to add.

Returns the end of the year for a given EOYEAR(<start_date>, start_date: The start date.
EOYEAR date. <years>) years: The number of years to add.

07
RAJAT SAXENA
PowerBI DAX CHEAT SHEET

FUNCTION NAME DESCRIPTION SYNTAX PARAMETERS

start_date: Starting date.

Returns the number of working days NETWORKDAYS(<start_date>,
NETWORKDAYS between two dates. <end_date>, [<holidays>])
end_date: Ending date.
holidays: Optional holiday dates to exclude.

NEXT Returns the next period for a given date. NEXT(<dates>) dates: The date column.

NEXTDAY Returns the next day for a given date. NEXTDAY(<dates>) dates: The date column.

Returns the next month for a given dates: The date column.
NEXTMONTH NEXTMONTH(<dates>)
date.

Returns the next quarter for a given NEXTQUARTER(<dates>) dates: The date column.
NEXTQUARTER date.

Returns the next year for a given

NEXTYEAR date.
NEXTYEAR(<dates>) dates: The date column.

Returns the previous period for a

PREVIOUS given date.
PREVIOUS(<dates>) dates: The date column.

08
RAJAT SAXENA
PowerBI DAX CHEAT SHEET

FUNCTION NAME DESCRIPTION SYNTAX PARAMETERS

Returns the previous day for a given

PREVIOUSDAY date.
PREVIOUSDAY(<dates>) dates: The date column.

Returns the previous month for a given

PREVIOUSMONTH date. PREVIOUSMONTH(<dates>) dates: The date column.

PREVIOUSQUARTER Returns the next day for a given date. PREVIOUSQUARTER(<dates>) dates: The date column.

Returns the next month for a given dates: The date column.
PREVIOUSYEAR PREVIOUSYEAR(<dates>)
date.

Sorts the data based on a column or ORDERBY(<expression>, expression: The expression or column to sort.
ORDERBY expression. <sort_order>) sort_order: ASC/DESC.

dates: Date column.

Returns a table containing parallel PARALLELPERIOD(<dates>,
number_of_intervals: The number of intervals to
PARALLELPERIOD period data, based on the given <number_of_intervals>,
go forward or back.
interval. <interval>)
interval: Year, quarter, month.

Evaluates an expression partitioned PARTITIONBY(<expression>, expression: The expression to evaluate.

PARTITIONBY by a certain group. <group>) group: The group to partition by.

RAJAT SAXENA 09
PowerBI DAX CHEAT SHEET
FUNCTION NAME DESCRIPTION SYNTAX PARAMETERS

Returns a delimited text string with child_column: The column representing child IDs.
PATH(<child_column>,
PATH the IDs of all parents to the current
<parent_column>) parent_column: The column representing parent IDs.
row.

Checks if a specific ID exists within the path: The path to search.

PATHCONTAINS path returned by PATH. PATHCONTAINS(<path>, <id>)
id: The ID to search for.

Returns a random number between 0

RAND and 1.
RAND() No parameters.

Returns a random integer between RANDBETWEEN(<lower>, lower: The lower bound.

RANDBETWEEN two numbers. <upper>) upper: The upper bound.

number: The number to rank.

Returns the rank of a value in a list of RANK(<number>, <column>, column: The column containing the list of
RANK numbers. <order>) numbers.
order: ASC/DESC.

table: The table to rank.

Returns the rank of a number in a RANKX(<table>, <expression>, expression: The expression to evaluate.
RANKX table or column. [, <value>, <order>]) value: Optional base value.
order: Optional ASC/DESC.

old_text: The text to modify.

REPLACE(<old_text>,
Replaces part of a string with another start_num: Starting position.
REPLACE string.
<start_num>, <num_chars>,
<new_text>) num_chars: Number of characters to replace.
new_text: The new text.

RAJAT SAXENA 10
PowerBI DAX CHEAT SHEET

FUNCTION NAME DESCRIPTION SYNTAX PARAMETERS

Repeats a text string a specified text: The text to repeat.

REPT number of times.
REPT(<text>, <number_times>) number_times: The number of times to repeat the text.

Creates a summary report that includes

ROLLUP subtotals and totals.
ROLLUP(<column>, ...) column: The column to roll up and summarize.

column: The column to affect.

ROLLUPADDDISSUBTOTAL(<co
ROLLUPADDDISSUBTOTAL Adds or removes subtotals to a rollup.
lumn>, <is_subtotal>)
is_subtotal: TRUE to add subtotals, FALSE to
remove.

Rounds a number to a specified ROUND(<number>, number: The number to round.

ROUND number of digits. <num_digits>) num_digits: The number of digits to round to.

ROUNDUP(<number>, number: The number to round.

ROUNDUP Rounds a number up, away from zero. <num_digits>) num_digits: The number of digits to round to.

Rounds a number down, towards ROUNDDOWN(<number>, number: The number to round.

ROUNDDOWN zero. <num_digits>) num_digits: The number of digits to round to.

name: The column name.

ROW Returns a single row of values. ROW(<name>, <expression>)
expression: The expression for the value.

RAJAT SAXENA 11
PowerBI DAX CHEAT SHEET

FUNCTION NAME DESCRIPTION SYNTAX PARAMETERS

Returns the row number of the

ROWNUMBER current row in the dataset.
ROWNUMBER() No parameters.

table: The dataset to sample from.

SAMPLE(<table>, <percentage>,
SAMPLE Returns a sample from a dataset.
<order>)
percentage: The percentage of rows to sample.
order: Sorting order.

Returns a sample for the same period SAMPLEPERIODLASTYEAR(<da

SAMPLEPERIODLASTYEAR in the previous year tes>)
dates: The date column.

find_text: The text to find.

SEARCH(<find_text>,
within_text: The text to search in.
SEARCH Finds one text string within another. <within_text>, [, <start_num>])
start_num: Optional start position.

SELECTEDCOLUMNS(<table>,
Returns the selected columns from a table: The table to retrieve columns from.
SELECTEDCOLUMNS <columnName1>,
table. columnName: The names of columns to retrieve.
<columnName2>, ...)

Returns the measure currently No parameters.

SELECTEDMEASURE selected.
SELECTEDMEASURE()

column: The column from which to retrieve the

Returns the value selected from a SELECTEDVALUE(<column>, selected value.
SELECTEDVALUE column. [<alternateValue>]) alternateValue: Optional value to return if no value
is selected.

RAJAT SAXENA 12
PowerBI DAX CHEAT SHEET

FUNCTION NAME DESCRIPTION SYNTAX PARAMETERS

Returns the first date of the month for

STARTOFMONTH a given date.
STARTOFMONTH(<dates>) dates: The date column.

Returns the first date of the quarter for a

STARTOFQUARTER given date.
STARTOFQUARTER(<dates>) dates: The date column.

find_text: The text to find.

Returns the first date of the year for a within_text: The text to search in.
STARTOFYEAR given date.
STARTOFYEAR(<dates>)
start_num: Optional start position.

Evaluates an expression against a SWITCH(<expression>, expression: The expression to evaluate.

<value1>, <result1>, [<value2>, value: Values to compare.
SWITCH list of values and returns the
corresponding result. <result2>, ...]) result: Result for each value.

hour: The hour.

Returns the time based on hour, TIME(<hour>, <minute>,
TIME minute: The minutes.
minute, and second. <second>)
second: The seconds.

Converts a time in text format to a time_text: The time text to convert.

TIMEVALUE time serial number.
TIMEVALUE(<time_text>)

n_value: The number of rows.

Returns the top N rows from a table TOPN(<n_value>, <table>, table: The table to retrieve rows from.
TOPN based on an expression. <expression>, <order>) expression: The sorting expression.
order: Sorting order.

RAJAT SAXENA 13
 14

PowerBI DAX CHEAT SHEET

FUNCTION NAME DESCRIPTION SYNTAX PARAMETERS

Removes leading and trailing spaces

TRIM from a text string.
TRIM(<text>) text: The text to trim.

TRUE Returns the logical value TRUE. TRUE() No parameters.

table: any DAX expression returning a data table.

Adds calculated columns to the table ADDCOLUMNS(<table>, name: name given to the column, enclosed in
ADD COLUMNS or the given expression. <name>, <expression>) double quotes.

Returns the arithmetic mean of all AVERAGE(<column>) column: column containing the values to average.
AVERAGE numbers in a column.

Counts the number of cells in a COUNT(<column>) column: column containing the values to count.
COUNT column containing non-blank values.

Counts the number of distinct values column: column containing the values to find
DISTINCT COUNT in a column.
DISTINCTCOUNT(<column>) distinct values.

Returns the largest value from a

column: column containing the values for which to
MAX column or between two scalar MAX(<column>)
find the greatest value.
expressions.

RAJAT SAXENA 14
PowerBI DAX CHEAT SHEET

FUNCTION NAME DESCRIPTION SYNTAX PARAMETERS

Returns the median of the numbers in column: column containing the numbers for which to
MEDIAN a column.
MEDIAN(<column>) calculate the median value.

Returns the smallest value in a column or column: column containing the values for which to
MIN between two scalar expressions.
MIN(<column>)
find the smallest value.

column: column containing the values that define

Returns the k-th percentile of values PERCENTILE.EXC(<column>, the relative position.
PERCENTILE.EXC in a range, excluding 0..1 limits. <k>) k: percentile from the range 0-1, excluding limits.

TOPN(<n_value>, <table>, n_value: number of rows to return.

Returns the top N rows of the
TOPN specified table.
<orderBy_expression>[, table: any DAX expression returning a table of data
<orderBy_expression>]...) whose top rows are to be returned. :

Returns the variance for the entire columnName: name of an existing column using
VAR.P VAR.P(<columnName>)
population. standard DAX syntax. Cannot be an expression.

Counts the number of distinct values column: column containing the values to find
DISTINCT COUNT in a column.
DISTINCTCOUNT(<column>) distinct values.

Returns the largest value from a

column: column containing the values for which to
MAX column or between two scalar MAX(<column>)
find the greatest value.
expressions.

RAJAT SAXENA 15
PowerBI DAX CHEAT SHEET

FUNCTION NAME DESCRIPTION SYNTAX PARAMETERS

Returns TRUE if the values of all CONTAINS(<table>, table: any DAX expression returning a data table.
CONTAINS referenced columns exist or are <columnName>, <value>[, columnName: name of an existing column, specified
using standard DAX syntax.
contained in those columns; <columnName>, <value>...])

Checks if a value is empty and returns

ISBLANK TRUE or FALSE.
ISBLANK(<value>) value: value or expression to test.

Checks if a value is an error and value: boolean TRUE if the value is an error;
ISERROR returns TRUE or FALSE.
ISERROR(<value>)
otherwise, FALSE.

Returns TRUE if the number is even number: value to test. If not an integer, it is
ISEVEN ISEVEN(<number>) truncated.
or FALSE if the number is odd.

Checks if a value is a logical value

ISLOGICAL (TRUE or FALSE) and returns TRUE or ISLOGICAL(<value>) value: value to test.
FALSE.

Checks if a value is a number and value: value to test.

ISNUMBER returns TRUE or FALSE.
ISNUMBER(<value>)

Checks if a value is text and returns

ISTEXT TRUE or FALSE.
ISTEXT(<value>) value: value to test.

RAJAT SAXENA 16
PowerBI DAX CHEAT SHEET
FUNCTION NAME DESCRIPTION SYNTAX PARAMETERS

Returns TRUE if the values of all CONTAINS(<table>, table: any DAX expression returning a data table.
CONTAINS referenced columns exist or are <columnName>, <value>[, columnName: name of an existing column, specified
using standard DAX syntax.
contained in those columns; <columnName>, <value>...])

Checks if a value is empty and returns

ISBLANK TRUE or FALSE.
ISBLANK(<value>) value: value or expression to test.

Checks if a value is an error and value: boolean TRUE if the value is an error;
ISERROR returns TRUE or FALSE.
ISERROR(<value>)
otherwise, FALSE.

Returns TRUE if the number is even number: value to test. If not an integer, it is
ISEVEN ISEVEN(<number>) truncated.
or FALSE if the number is odd.

Checks if a value is a logical value

ISLOGICAL (TRUE or FALSE) and returns TRUE or ISLOGICAL(<value>) value: value to test.
FALSE.

Checks if a value is a number and value: value to test.

ISNUMBER returns TRUE or FALSE.
ISNUMBER(<value>)

Checks if a value is text and returns

ISTEXT TRUE or FALSE.
ISTEXT(<value>) value: value to test.

RAJAT SAXENA 17