@AVNEET.

BUILDS

THE DAX
CHEAT SHEET
YOU’LL
Actually
USE
Every function category, operator, and statement you need for Power BI — all in
one place. Bookmark it. Screenshot it. Use it.

@[Link] Page 1
 01 MATH & STATISTICAL

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

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

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

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

MEDIAN(<column>)
Returns the median of a column.

GEOMEAN(<column>)
Calculates the geometric mean of a column.

COUNT(<column>)
Returns the number of cells with non-blank values.

COUNTA(<column>)
Counts the number of rows from an expression that evaluates to a non-
blank value.

DIVIDE(<numerator>, <denominator> [, <alt>])

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

MIN(<column>) / MAX(<column>)
Returns the minimum or maximum value of a column.

DISTINCTCOUNT(<column>)
Counts the number of distinct values in a column.

RANKX(<table>, <expression> [, <value>, <order>, <ties>])

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

@[Link] Page 2
 02 FILTER FUNCTIONS

FILTER(<table>, <filter>)
Returns a subset of another table or expression.

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

Evaluates an expression in a modified filter context.

HASONEVALUE(<columnName>)
Returns TRUE when the context for columnName has been filtered down to
one distinct value only; otherwise divides by 0.

ALLNOBLANKROW(<table> | <column> [, <column>])

Returns all rows in a table, or all values in a column, ignoring any blank rows.

ALL([<table> | <column> [, <column>]])

Returns all rows in a table or all values in a column, ignoring any filters that
have been applied.

ALLEXCEPT(<table>, <column> [, <column>])

Returns all rows in a table except for those rows affected by the specified
column filters.

REMOVEFILTERS([<table> | <column> [, <column>]])

Clears all filters from designated tables or columns.

@[Link] Page 3
 03 LOGICAL FUNCTIONS

IF(<logical_test>, <value_if_true> [, <value_if_false>])

Checks a condition and returns a certain value depending on whether it is
TRUE or FALSE.

AND(<logical_1>, <logical_2>)
Checks both arguments are TRUE; returns TRUE if both are TRUE, otherwise
returns FALSE.

OR(<logical_1>, <logical_2>)
Checks whether one of the arguments is TRUE; returns FALSE if both
arguments are FALSE.

NOT(<logical>)
Changes TRUE to FALSE and vice versa.

SWITCH(<expression>, <value>, <result> [, <value>, <result>] [, <else>])

Evaluates an expression against a list of values and returns one of multiple
possible result expressions.

IFERROR(<value>, <value_if_error>)
Returns the value itself if no error; returns the value_if_error if the expression is
an error.

@[Link] Page 4
 04 DATE & TIME FUNCTIONS

CALENDAR(<start_date>, <end_date>)
Returns a table with a single column "Date" that contains a contiguous set
of dates.

DATE(<year>, <month>, <day>)

Returns the specified date in datetime format.

DATEDIFF(<date_1>, <date_2>, <interval>)

Returns the number of units between two dates.

DATEVALUE(<date_text>)
Converts a date in text to a date in datetime format.

DAY / WEEKNUM / MONTH / QUARTER / YEAR

Extract the day (1–31), week number, month (1–12), quarter (1–4), or year from
a date.

@[Link] Page 5
 05 TIME INTELLIGENCE

DATEADD(<dates>, <number_of_intervals>, <interval>)

Moves a date by a specific interval.

DATESBETWEEN(<dates>, <start>, <end>)

Returns dates between two specified dates.

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

Evaluates the year-to-date value of the expression in the current context.

SAMEPERIODLASTYEAR(<dates>)
Returns a table of dates shifted one year back.

STARTOFMONTH / ENDOFMONTH(<dates>)
Returns the start or end of the month.

STARTOFQUARTER / ENDOFQUARTER(<dates>)
Returns the start or end of the quarter.

STARTOFYEAR / ENDOFYEAR(<dates>)
Returns the start or end of the year.

06 RELATIONSHIP FUNCTIONS

CROSSFILTER(<left_col>, <right_col>, <crossfiltertype>)

Specifies the cross-filtering direction to be used in a calculation.

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

@[Link] Page 6
 07 TABLE MANIPULATION

SUMMARIZE(<table>, <groupBy_col> [, <name>, <expression>])

Returns a summary table for the required totals over a set of groups.

DISTINCT(<table>)
Returns a table by removing duplicate rows from another table or
expression.

ADDCOLUMNS(<table>, <name>, <expression> [, …])

Adds calculated columns to the given table or table expression.

SELECTCOLUMNS(<table>, <name>, <expression> [, …])

Selects calculated columns from the given table or table expression.

GROUPBY(<table>, <groupBy_col> [, <name>, <expression>])

Creates a summary of the input table grouped by specific columns.

INTERSECT / UNION / NATURALLEFTOUTERJOIN

Set operations: intersection, union, or left join of two tables with matching
columns.

@[Link] Page 7
 08 TEXT FUNCTIONS

EXACT(<text_1>, <text_2>)
Checks if two strings are identical (case sensitive).

FIND(<find_text>, <within_text> [, <start>])

Returns the starting position of one text string within another (case sensitive).

FORMAT(<value>, <format_string>)
Converts a value to text in the specified number format.

LEFT / RIGHT(<text>, <num_chars>)

Returns characters from the start or end of a string.

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

LOWER / UPPER(<text>)
Converts all letters in a string to lowercase or uppercase.

TRIM(<text>)
Removes all spaces from a free text string.

CONCATENATE(<text_1>, <text_2>)
Joins two text strings into one string.

SUBSTITUTE(<text>, <old_text>, <new_text> [, <instance>])

Replaces existing text with new text in a string.

REPLACE(<old_text>, <start>, <num_chars>, <new_text>)

Replaces part of a string with a new string.

@[Link] Page 8
 09 INFORMATION FUNCTIONS

NAMEOF(<value>)
Returns the column or measure name of a value.

ISBLANK(<value>) / ISERROR(<value>)
Returns whether the value is blank or is an error.

ISLOGICAL / ISNUMBER / ISTEXT(<value>)

Checks whether a value is logical, a number, or text.

ISFILTERED(<table> | <column>)
Returns TRUE when there are direct filters on a column.

ISCROSSFILTERED(<table> | <column>)
Returns TRUE when there are crossfilters on a column.

USERPRINCIPALNAME()
Returns the user principal name or email address. No arguments.

10 DAX STATEMENTS

VAR <name> = <expression>

Stores the result of an expression as a named variable. Use RETURN after
the variable is defined.

COLUMN <name> = <expression>

Stores the result of an expression as a column in a table.

ORDER BY <table>[<column>] [DESC]

Defines the sort order of a column. Every column can be sorted in
ascending (ASC) or descending (DESC) way.

@[Link] Page 9