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. Have this cheat sheet at your fingertips

## 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"}`
Related

### What’s Driving the Data Literacy Skills Gap?

Explore the factors driving the data literacy skills gap, including demand for data-driven decision-making, technical proficiency, and the current state of data training.

Matt Crabtree

8 min

### What is Data Literacy? A Comprehensive Guide for Organizations

Discover the importance of data literacy in today's data-driven world.

### 8 Power BI Projects To Develop Your Skills

Explore our list of Power BI projects for beginner and intermediate learners across various different industries and use cases.

### Data Transformation with Power Query M in Power BI

Learn how to transform data with Power Query M in Power BI in this convenient cheat sheet!

### Working with Tables in Power Query M in Power BI

Learn how to work with tables in Power Query M in Power BI in this convenient cheat sheet!

### Power BI Dashboards vs Reports: A Comprehensive Guide

Explore the key differences and unique features of Power BI dashboards and reports. Learn how to leverage both tools effectively for data analysis, visualization, and decision-making. Maarten Van den Broeck

8 min

See MoreSee More