Introduction

DAX (Data Analysis Expressions) is the formula language used in Power BI to create calculated columns, measures, and custom visuals. DAX provides a variety of functions that allow you to manipulate data to gain insights. Here are descriptions and examples of the top 20 most commonly used DAX functions.

SUM

Returns the sum of values in a column. Useful for aggregating numeric data.

Example

JavaScript
SUM(Sales[Revenue])

AVERAGE

Returns the average (arithmetic mean) of values in a column.

Example

JavaScript
AVERAGE(Sales[Revenue])

MIN

Returns the minimum value in a column.

Example

JavaScript
MIN(Sales[Revenue])

MAX

Returns the maximum value in a column.

Example

JavaScript
MAX(Sales[Revenue])

COUNT

Returns the number of rows in a column, table, or expression. Excludes blank rows.

Example

JavaScript
COUNT(Sales[ProductID])

COUNTROWS

Returns the number of rows in a table, including blank rows.

Example

JavaScript
COUNTROWS(Sales)

DISTINCT COUNT

Returns the number of distinct values in a column.

Example

JavaScript
DISTINCT COUNT(Sales[CustomerID])

FILTER

Filters a table to only rows that meet a condition.

Example

JavaScript
FILTER(Sales, Sales[Country]="Canada")

CALCULATE

Performs calculations on a table expression, applying filters context. Allows What-If analysis.

Example

JavaScript
CALCULATE(SUM(Sales[Revenue]), Sales[Country]="Canada")

IF

Returns one value if a condition is met and another value if not. Similar to an IF statement in other languages.

Example

JavaScript
IF(SUM(Sales[Revenue])>1000000, "Met Goal", "Missed Goal")

SWITCH

Evaluates an expression against a list of cases and returns the result for the first matching case. Similar to a Switch statement.

Example

JavaScript
SWITCH(TRUE(), Sales[Country]="Canada", "North America", Sales[Country]="France", "Europe")

DATEADD

Returns a date increased or decreased by an interval (day, week, month, etc). Useful for date calculations.

Example

JavaScript
DATEADD(Sales[OrderDate], 7, DAY)

DATESINPERIOD

Returns the number of days in a specified time period relative to a date column.

Example

JavaScript
DATESINPERIOD(Sales[OrderDate], LAST DATE(Calendar[Date]), MONTH)

DATEDIFF

Returns the number of intervals between two dates. Intervals can be days, months, years, etc.

Example

JavaScript
DATEDIFF(Sales[ ShipDate], Sales[OrderDate], DAY)

TODAY

Returns the current date.

Example

JavaScript
TODAY()

EARLIER

Refers to an outer evaluation context to enable iterative calculations. Used with CALCULATE.

Example

JavaScript
CALCULATE( AVERAGE(Sales[Revenue]), FILTER(ALL(Sales), Sales[Date]=EARLIER(Sales[Date]) )

RANKX

Returns the rank of a value in a list based on specified order criteria. Useful for advanced analytics.

Example

JavaScript
RANKX(ALL(Sales), Sales[Revenue], , DESC, Dense)

CALCULATETABLE

Returns a table created by altering the filter context. Allows for advanced table manipulation.

Example

JavaScript
CALCULATETABLE( ADDCOLUMNS(Sales, "Prev Year", [Revenue] - CALCULATE([Revenue], DATEADD(Sales[Date], -12, MONTH)) ))

USE RELATIONSHIP

Enables filtering related tables implicitly via relationships without specifying cross-filter direction.

Example

JavaScript
USERELATIONSHIP(Sales[CustomerID], Customers[CustomerID])

SELECT COLUMNS

Returns a table with only specified columns. Useful for shaping data output.

Example

JavaScript
SELECTCOLUMNS(Sales, "Product", Sales[Product], "Revenue", Sales[Revenue] )

Summary

These top 20 DAX functions allow you to aggregate, filter, manipulate dates, rank, iterate, relate data models, and shape data for reporting. Mastering these functions is key to building powerful BI solutions with Power BI. The functions provide the ability to handle complex data modeling and deliver actionable insights.