Power BI for Business Intelligence DAX Functions

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"}