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.