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
SUM(Sales[Revenue])
AVERAGE
Returns the average (arithmetic mean) of values in a column.
Example
AVERAGE(Sales[Revenue])
MIN
Returns the minimum value in a column.
Example
MIN(Sales[Revenue])
MAX
Returns the maximum value in a column.
Example
MAX(Sales[Revenue])
COUNT
Returns the number of rows in a column, table, or expression. Excludes blank rows.
Example
COUNT(Sales[ProductID])
COUNTROWS
Returns the number of rows in a table, including blank rows.
Example
COUNTROWS(Sales)
DISTINCT COUNT
Returns the number of distinct values in a column.
Example
DISTINCT COUNT(Sales[CustomerID])
FILTER
Filters a table to only rows that meet a condition.
Example
FILTER(Sales, Sales[Country]="Canada")
CALCULATE
Performs calculations on a table expression, applying filters context. Allows What-If analysis.
Example
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
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
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
DATEADD(Sales[OrderDate], 7, DAY)
DATESINPERIOD
Returns the number of days in a specified time period relative to a date column.
Example
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
DATEDIFF(Sales[ ShipDate], Sales[OrderDate], DAY)
TODAY
Returns the current date.
Example
TODAY()
EARLIER
Refers to an outer evaluation context to enable iterative calculations. Used with CALCULATE.
Example
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
RANKX(ALL(Sales), Sales[Revenue], , DESC, Dense)
CALCULATETABLE
Returns a table created by altering the filter context. Allows for advanced table manipulation.
Example
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
USERELATIONSHIP(Sales[CustomerID], Customers[CustomerID])
SELECT COLUMNS
Returns a table with only specified columns. Useful for shaping data output.
Example
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.