Skip to main content
HomeCheat sheetsPower BI

DAX Cheat Sheet

This cheat sheet is your handy companion when working with DAX formulas and statements in Power BI.
May 2022  · 7 min read

Power BI is one of the most robust and powerful business intelligence tools out there. One of the most powerful features of Power BI is DAX (Data Analysis Expressions), which is a formula expression designed for advanced data analysis. DAX formulas contain functions, operators, statements, and more. This cheat sheet is designed to be your handy companion when working with DAX in Power BI. 

Explore DAX in more detail by starting our course now.

dax cheat sheet

Have this cheat sheet at your fingertips

Download PDF

Maths & Statistical Functions

  • SUM(<column>) Adds all the numbers in a column.
  • SUMX(<table>, <expression>) Returns the sum of an expression evaluated for each row in a table.
  • AVERAGE(<column>) Returns the average (arithmetic mean) of all the numbers in a column.
  • AVERAGEX(<table>, <expression>) Calculates the average (arithmetic mean) of a set of expressions evaluated over a table.
  • MEDIAN(<column>) Returns the median of a column.
  • MEDIANX(<table>, <expression>) Calculates the median of a set of expressions evaluated over a table.
  • GEOMEAN(<column>) Calculates the geometric mean of a column.
  • GEOMEANX(<table>, <expression>) Calculates the geometric mean of a set of expressions evaluated over a table.
  • COUNT(<column>) Returns the number of cells in a column that contains non-blank values.
  • COUNTX(<table>, <expression>) Counts the number of rows from an expression that evaluates to a non-blank value.
  • DIVIDE(<numerator>, <denominator> [,<alternateresult>]) Performs division and returns alternate result or BLANK() on division by 0.
  • MIN(<column>) Returns a minimum value of a column.
  • MAX(<column>) Returns a maximum value of a column.
  • COUNTROWS([<table>]) Counts the number of rows in a table.
  • 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 in the table argument.

Filter Functions

  • FILTER(<table>, <filter>) Returns a table that is a subset of another table or expression.
  • CALCULATE(<expression>[, <filter1> [, <filter2> [, …]]]) Evaluates an expression in a filter context.
  • HASONEVALUE(<columnName>) Returns TRUE when the context for columnName has been filtered down to one distinct value only. Otherwise, it is FALSE.
  • ALLNOBLANKROW(<table> | <column>[, <column>[, <column>[,…]]]) Returns a table that is a subset of another table or expression.
  • ALL([<table> | <column>[, <column>[, <column>[,…]]]]) Returns all the rows in a table, or all the values in a column, ignoring any filters that might have been applied.
  • ALLEXCEPT(<table>, <column>[, <column>[,..]]) Returns all the rows in a table except for those rows that are affected by the specified column filters.
  • REMOVEFILTERS([<table> | <column>][, <column>[, <column>[,…]]]]) Clear all filters from designated tables or columns.

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 whether both arguments are TRUE, and returns TRUE if both arguments are TRUE. Otherwise, it returns FALSE.
  • OR(<logical 1>, <logical 2>) Checks whether one of the arguments is TRUE to return TRUE. The function 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 possible results
  • IFERROR(<value>, <value_if_error>) Returns value_if_error if the first expression is an error and the value of the expression itself otherwise.

Date & Time Functions

  • CALENDAR(<start_date>, <end_date>) Returns a table with a single column named "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 as defined in <interval>.
  • DATEVALUE(<date_text>) Converts a date in text to a date in datetime format.
  • DAY(<date>) Returns a number from 1 to 31 representing the day of the month.
  • WEEKNUM(<date>) Returns weeknumber in the year.
  • MONTH(<date>) Returns a number from 1 to 12 representing a month.
  • QUARTER(<date>) Returns a number from 1 to 4 representing a quarter.

Time Intelligence Functions

  • DATEADD(<dates>, <number_of_intervals>, <interval>) Moves a date by a specific interval.
  • DATESBETWEEN(<dates>, <date_1>, <date_2>) Returns the dates between 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 that contains a column of dates shifted one year back in time.
  • STARTOFMONTH(<dates>) // ENDOFMONTH(<dates>) Returns the start // end of the month.
  • STARTOFQUARTER(<dates>) // ENDOFQUARTER(<dates>) Returns the start // end of the quarter.
  • STARTOFYEAR(<dates>) // ENDOFYEAR(<dates>) Returns the start // end of the quarter.

Relationship Functions

  • CROSSFILTER(<left_column>, <right_column>, <crossfiltertype>) Specifies the cross-filtering direction to be used in a calculation.
  • RELATED(<column>) Returns a related value from another table.

Table Manipulation Functions

  • SUMMARIZE(<table>, <groupBy_columnName>[, <groupBy_columnName>]…[, <name>, <expression>]…) Returns a summary table for the requested totals over a set of groups.
  • DISTINCT(<table>) Returns a table by removing duplicate rows from another table or expression.
  • ADDCOLUMNS(<table>, <name>, <expression>[, <name>, <expression>]…) Adds calculated columns to the given table or table expression.
  • SELECTCOLUMNS(<table>, <name>, <expression>[, <name>, <expression>]…) Selects calculated columns from the given table or table expression.
  • GROUPBY(<table> [, <groupBy_columnName>[, [<column_name>] [<expression>]]…) Create a summary of the input table grouped by specific columns.
  • INTERSECT(<left_table>, <right_table>) Returns the rows of the left-side table that appear in the right-side table.
  • NATURALINNERJOIN(<left_table>, <right_table>) Joins two tables using an inner join.
  • NATURALLEFTOUTERJOIN(<left_table>, <right_table>) Joins two tables using a left outer join.
  • UNION(<table>, <table>[, <table> [,…]]) Returns the union of tables with matching columns.

Text Functions

  • EXACT(<text_1>, <text_2>) Checks if two strings are identical (EXACT() is case sensitive).
  • FIND(<text_tofind>, <in_text>) Returns the starting position a text within another text (FIND() is case sensitive).
  • FORMAT(<value>, <format>) Converts a value to a text in the specified number format.
  • LEFT(<text>, <num_chars>) Returns the number of characters from the start of a string.
  • RIGHT(<text>, <num_chars>) Returns the number of characters from the end of a string.
  • LEN(<text>) Returns the number of characters in a string of text.
  • LOWER(<text>) Converts all letters in a string to lowercase.
  • UPPER(<text>) Converts all letters in a string to uppercase.
  • TRIM(<text>) Remove all spaces from a text string.
  • CONCATENATE(<text_1>, <text_2>) Joins two strings together into one string.
  • SUBSTITUTE(<text>, <old_text>, <new_text>, <instance_num>) Replaces existing text with new text in a string.
  • REPLACE(<old_text>, <start_posotion>, <num_chars>, <new_text>) Replaces part of a string with a new string.

Information Functions

  • COLUMNSTATISTICS() Returns statistics regarding every column in every table. This function has no arguments.
  • NAMEOF(<value>) Returns the column or measure name of a value.
  • ISBLANK(<value>) // ISERROR(<value>) Returns whether the value is blank // an error.
  • ISLOGICAL(<value>) Checks whether a value is logical or not.
  • ISNUMBER(<value>) Checks whether a value is a number or not.
  • 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. This function has no arguments.

DAX Statements

  • VAR(<name> = <expression>) Stores the result of an expression as a named variable. To return the variable, use RETURN after the variable is defined.
  • COLUMN(<table>[<column>] = <expression>) Stores the result of an expression as a column in a table.
  • ORDER BY(<table>[<column>]) Defines the sort order of a column. Every column can be sorted in ascending (ASC) or descending (DESC) way.

DAX Operators

Comparison operators Meaning
= Equal to
= = Strict equal to 
> Great than
< Smaller than
> = Greater than or equal to
= < Smaller than or equal to
< > Not equal to 
Text operator Meaning Example
& Concatenates text values Concatenates text values | [City]&", "&[State]
Logical operator Meaning Example
&& AND condition ([City] = "Bru") && ([Return] = "Yes"))
|| OR condition ([City] = "Bru") || ([Return] = "Yes"))
IN {}  OR condition for each row Product[Color] IN {"Red", "Blue", "Gold"}
Topics
Related

cheat sheet

Power BI Cheat Sheet

In this Power BI Cheat Sheet, you'll get the download on creating your first visualization, top visualizations in Power BI, using DAX, and more.
Richie Cotton's photo

Richie Cotton

8 min

cheat sheet

SQL Basics Cheat Sheet

With this SQL cheat sheet, you'll have a handy reference guide to basic querying tables, filtering data, and aggregating data
Richie Cotton's photo

Richie Cotton

5 min

cheat sheet

Excel Formulas Cheat Sheet

Learn the basics of Excel with our quick and easy cheat sheet. Have the basics of formulas, operators, math functions and more at your fingertips.
Richie Cotton's photo

Richie Cotton

18 min

cheat sheet

SQL Joins Cheat Sheet

With this SQL Joins cheat sheet, you'll have a handy reference guide to joining data in SQL.
Richie Cotton's photo

Richie Cotton

6 min

cheat sheet

Data Science Cheat Sheet for Business Leaders

This cheat sheet guides you through the basics of how data science can help your business, including building your data science team and the common steps in the data science workflow.
Joyce Chiu's photo

Joyce Chiu

6 min

tutorial

Power BI DAX Tutorial for Beginners

Learn what DAX is and discover the fundamental DAX syntax and functions you'll need to take your Power BI skills to the next level.
DataCamp Team's photo

DataCamp Team

9 min

See MoreSee More