Exploring Aggregation, Date-Time, and Filter Functions in DAX

Introduction

DAX (Data Analysis Expressions) is the formula and query language used in Power BI to create calculated columns, measures, and advanced analytics.

Here is a detailed description of all the categories of DAX functions with examples. There are many categories of DAX functions. So, I have I have separated it into 4 parts. Here is the first part. You can also visit my next article for other parts i.e.

DAX aggregation functions

SUM function in DAX

Adds together all the values in a column. Useful for totaling data.

Example

SUM(Sales[Revenue])

Returns the total sum of the Revenue column in the Sales table.

AVERAGE function in DAX

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

Example

AVERAGE(Sales[ProfitMargin])

Returns the average value of the ProfitMargin column in the Sales table.

MIN function in DAX

Returns the minimum value in a column. Useful for finding the lowest values.

Example

MIN(Sales[UnitsSold])

Returns the minimum value in the UnitsSold column in the Sales table.

MAX function in DAX

Returns the maximum value in a column. Useful for finding the highest values.

Example

MAX(Sales[UnitsSold])

Returns the maximum value in the UnitsSold column in the Sales table.

COUNT function in DAX

Returns the count of rows in a table or column, excluding blank rows.

Example

COUNT(Sales[ProductKey])

Returns a count of all the rows in the ProductKey column in the Sales table, excluding blanks.

COUNTA function in DAX

Returns the count of values in a column, including blank rows.

Example

COUNTA(Sales[CustomerKey])

Returns a count of all values in the CustomerKey column, including blanks.

DISTINCTCOUNT function in DAX

Returns the number of distinct values in a column. Removes duplicates.

Example

DISTINCTCOUNT(Sales[ProductKey])

DAX Date and Time Functions

DATEADD function in DAX

Returns a date increased or decreased by a specified time interval.

Syntax

DATEADD(dates, number_to_add, interval)

Example

DATEADD(Sales[OrderDate], 7, DAY)

Returns the OrderDate from the Sales table increased by 7 days.

DATESINPERIOD function in DAX

Returns the number of days in a period defined by an anchor date and unit of time.

Syntax

DATESINPERIOD(anchor_date, start_end_date, interval)

Example

DATESINPERIOD(Sales[OrderDate], LASTDATE(Calendar[Date]), MONTH)

Returns the number of days in the month for each OrderDate in the Sales table.

DATEDIFF function in DAX

Returns the number of intervals between two dates.

Syntax

DATEDIFF(start_date, end_date, interval)

Example

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

Returns the number of days between the ShipDate and OrderDate from the Sales table.

ENDDATE function in DAX

Returns the last date of the period containing the given date.

Syntax

ENDDATE(date, interval)

Example

ENDDATE(Sales[OrderDate], MONTH)

Returns the last day of the month for each OrderDate in the Sales table.

STARTOFYEAR function in DAX

Returns start of the year containing the given date.

Syntax

STARTOFYEAR(date)

Example

STARTOFYEAR(Sales[OrderDate])

Returns January 1st of the year for each OrderDate in the Sales table.

DAX filter functions

FILTER function in DAX

Returns a table filtered to rows where the filter condition is met.

Syntax

FILTER(table, filter_condition)

Example

FILTER(Sales, Sales[Country] = "France")

Returns the Sales table filtered to rows only for France.

ALL function in DAX

Returns all rows in a table, overriding any filters.

Syntax

ALL(table)

Example

CALCULATE(SUM(Sales[Revenue]), ALL(Sales))

Calculates total revenue overriding any filters on the Sales table.

ALLEXCEPT function in DAX

Returns all rows except those filtered by specified columns.

Syntax

ALLEXCEPT(table, excluded_columns)

Example

CALCULATE(SUM(Sales[Revenue]), ALLEXCEPT(Sales, Sales[Region]))

Calculates total revenue excluding any filters on the Region column only.

TOPN function in DAX

Returns top N rows sorted by the specified column.

Syntax

TOPN(N, table, sort_column, [sort order])

Example

TOPN(10, Sales, Sales[Revenue])

Returns top 10 rows from the Sales table sorted by Revenue column.

BOTTOMN function in DAX

Returns bottom N rows sorted by the specified column.

Syntax

BOTTOMN(N, table, sort_column, [sort order])

Example

BOTTOMN(10, Sales, Sales[Profit])

Returns bottom 10 rows from the Sales table sorted by Profit column.