Top 21 Common DAX Functions in Power BI: Ultimate guide

In this detailed guide, we will look into the Top 21 Common DAX Functions which are use for all sort of data analytics in Power BI.

In Power BI the ability to handle vast datasets combined with the power of DAX functions that makes it an invaluable tool for data professionals, business analysts, and decision-makers.

By leveraging these common DAX functions, you can transform raw data into actionable insights, driving better business outcomes and strategic planning in Power BI.

Related Article: What is KPI in Power BI? – Comprehensive Guide

What are DAX Functions?

DAX (Data Analysis Expressions) functions are powerful tools in Power BI, Excel Power Pivot, and SQL Server Analysis Services (SSAS).

DAXs enable data modeling and analysis by performing various calculations on data.

DAX functions are used to create calculated columns, measures, and tables, allowing for dynamic data analysis, time intelligence, and complex aggregations.

Top Common DAXs Functions in Power BI

Here are the 21 Common DAX functions that are used for calculation, measure and creating analysis for data variables in Power BI:

1. SUM

The SUM function adds all the numbers in a column. It’s commonly used to calculate totals in financial or transactional data.

This function is essential for aggregating data to provide insights into overall performance or trends.

Syntax:

SUM(<column>)

Example:

Total Sales = SUM(Sales[SalesAmount])

Guide:

1. Go to the data view.
2. Create a new measure in your Sales table.
3. Enter the above formula to calculate the total sales amount.

2. AVERAGE

The AVERAGE function computes the arithmetic mean of a column.

It is useful for finding the central tendency of numerical data, such as the average sales amount or average price.

Syntax:

AVERAGE(<column>)

Example:

Average Sales = AVERAGE(Sales[SalesAmount])

Guide:

1. Go to the data view.
2. Create a new measure in your Sales table.
3. Enter the above formula to calculate the average sales amount.

3. COUNT

The COUNT function counts the number of non-empty values in a column.

It’s useful for counting the number of transactions, customers, or any other entities.

Syntax:

COUNT(<column>)

Example:

Total Orders = COUNT(Orders[OrderID])

Guide:

1. Go to the data view.
2. Create a new measure in your Orders table.
3. Enter the above formula to count the number of orders.

4. DISTINCTCOUNT

The DISTINCTCOUNT function counts the number of unique values in a column.

It is helpful for identifying unique customers, products, or other unique entities.

Syntax:

DISTINCTCOUNT(<column>)

Example:

Unique Customers = DISTINCTCOUNT(Customers[CustomerID])

Guide:

1. Go to the data view.
2. Create a new measure in your Customers table.
3. Enter the above formula to count the unique customers.

5. MIN

The MIN function returns the smallest numeric value in a column.

It’s useful for finding the minimum sales amount, minimum price, or other minimum values in your dataset.

Syntax:

MIN(<column>)

Example:

Lowest Sale = MIN(Sales[SalesAmount])

Guide:

1. Go to the data view.
2. Create a new measure in your Sales table.
3. Enter the above formula to find the smallest sales amount.

6. MAX

The MAX function returns the largest numeric value in a column.

It’s used to find the maximum sales amount, maximum price, or other maximum values in your data.

Syntax:

MAX(<column>)

Example:

Highest Sale = MAX(Sales[SalesAmount])

Guide:

1. Go to the data view.
2. Create a new measure in your Sales table.
3. Enter the above formula to find the largest sales amount.

7. CALCULATE

The CALCULATE function evaluates an expression in a modified filter context.

It’s powerful for creating complex calculations that consider specific conditions or filters.

Syntax:

CALCULATE(<expression>, <filter1>, <filter2>, ...)

Example:

Sales 2023 = CALCULATE(SUM(Sales[SalesAmount]), YEAR(Sales[OrderDate]) = 2023)

Guide:

1. Go to the data view.
2. Create a new measure in your Sales table.
3. Enter the above formula to calculate total sales for the year 2023.

8. SUMX

The SUMX function returns the sum of an expression evaluated for each row in a table.

It’s useful for calculating totals based on complex expressions involving multiple columns.

Syntax:

SUMX(<table>, <expression>)

Example:

Total Revenue = SUMX(Sales, Sales[Quantity] * Sales[UnitPrice])

Guide:

1. Go to the data view.
2. Create a new measure in your Sales table.
3. Enter the above formula to calculate total revenue by multiplying quantity and unit price for each sale.

9. AVERAGEX

The AVERAGEX function calculates the average of an expression evaluated for each row in a table.

It’s used for more complex averages that involve multiple columns.

Syntax:

AVERAGEX(<table>, <expression>)

Example:

Average Revenue = AVERAGEX(Sales, Sales[Quantity] * Sales[UnitPrice])

Guide:

1. Go to the data view.
2. Create a new measure in your Sales table.
3. Enter the above formula to calculate the average revenue per sale.

10. COUNTX

The COUNTX function counts the number of non-blank results for an expression evaluated for each row in a table.

It’s useful for counting rows that meet specific conditions.

Syntax:

COUNTX(<table>, <expression>)

Example:

Count Products Sold = COUNTX(Sales, Sales[ProductID])

Guide:

1. Go to the data view.
2. Create a new measure in your Sales table.
3. Enter the above formula to count the number of products sold.

11. RELATED

The RELATED function retrieves a related value from another table using an established relationship.

It’s useful for bringing in related data without joining tables in Power Query.

Syntax:

RELATED(<column>)

Example:

Customer Name = RELATED(Customers[CustomerName])

Guide:

1. Ensure you have a relationship between the Sales and Customers tables.
2. Create a new column in the Sales table.
3. Enter the above formula to retrieve the customer name from the Customers table.

12. RELATEDTABLE

The RELATEDTABLE function returns a table that contains all rows related to the current row from another table.

It’s useful for performing calculations based on related rows.

Syntax:

RELATEDTABLE(<table>)

Example:

Orders Count = COUNTROWS(RELATEDTABLE(Orders))

Guide:

1. Ensure you have a relationship between the Customers and Orders tables.
2. Create a new measure in the Customers table.
3. Enter the above formula to count the number of orders related to a particular customer.

13. IF

The IF function checks a condition and returns one value if the condition is true and another value if it is false.

It’s essential for conditional calculations and classifications.

Syntax:

IF(<logical_test>, <value_if_true>, <value_if_false>)

Example:

Sales Category = IF(Sales[SalesAmount] > 1000, "High", "Low")

Guide:

1. Create a new column in the Sales table.
2. Enter the above formula to categorize sales as “High” or “Low” based on the sales amount.

14. SWITCH

The SWITCH function evaluates an expression against a list of values and returns the corresponding result for the first matching value.

It simplifies complex nested IF statements.

Syntax:

SWITCH(<expression>, <value>, <result>[, <value>, <result>]…[, <else>])

Example:

Product Category = SWITCH(Product[CategoryID], 1, "Electronics", 2, "Clothing", "Other")

Guide:

1. Create a new column in the Product table.
2. Enter the above formula to return a product category name based on category ID.

15. DIVIDE

The DIVIDE function performs division and handles divide-by-zero cases by returning an alternate result if the denominator is zero.

It ensures safe division operations in calculations.

Syntax:

DIVIDE(<numerator>, <denominator>[, <alternateResult>])

Example:

Profit Margin = DIVIDE(Sales[Profit], Sales[SalesAmount], 0)

Guide:

1. Create a new measure in the Sales table.
2. Enter the above formula to calculate profit margin, handling divide-by-zero errors.

16. VALUES

The VALUES function returns a one-column table that contains the distinct values from the specified column.

It’s used in creating slicers, filters, and for dynamic calculations.

Syntax:

VALUES(<column>)

Example:

Distinct Products = VALUES(Sales[Product

ID])

Guide:

1. Create a new table using the above formula to get a list of distinct products sold.

17. ALL

The ALL function removes all filters from the specified column or table, effectively ignoring any filters that are applied.

It’s useful for creating calculations that need to consider the entire dataset.

Syntax:

ALL(<column|table>)

Example:

All Sales = CALCULATE(SUM(Sales[SalesAmount]), ALL(Sales))

Guide:

1. Create a new measure in the Sales table.
2. Enter the above formula to calculate total sales ignoring all filters.

18. FILTER

The FILTER function returns a table that represents a subset of another table or expression.

It’s used to filter data based on complex conditions and can be nested within other DAX functions.

Syntax:

FILTER(<table>, <expression>)

Example:

High Sales = FILTER(Sales, Sales[SalesAmount] > 1000)

Guide:

1. Create a new table using the above formula to filter the sales table to include only high sales.

19. EARLIER

The EARLIER function returns the current value of a column in an earlier row context.

It’s useful for nested row context operations, such as running totals or ranks within partitions.

Syntax:

EARLIER(<column>[, <number>])

Example:

Rank = RANKX(ALL(Sales), Sales[SalesAmount], Sales[SalesAmount], , DESC, DENSE)

Guide:

1. Create a new measure in the Sales table.
2. Enter the above formula to rank sales amounts in descending order.

20. RANKX

The RANKX function returns the ranking of a number in a list of numbers for each row in the table argument.

It’s useful for ranking products, salespeople, or other entities based on performance metrics.

Syntax:

RANKX(<table>, <expression>[, <value>[, <order>[, <ties>]]])

Example:

Product Rank = RANKX(ALL(Sales), Sales[SalesAmount])

Guide:

1. Create a new measure in the Sales table.
2. Enter the above formula to rank products based on sales amount.

21. FORMAT

The FORMAT function converts a value to the specified format, such as date formats or numeric formats.

It’s useful for displaying data in a user-friendly manner.

Syntax:

FORMAT(<value>, <format_string>)

Example:

Formatted Date = FORMAT(Sales[OrderDate], "DD/MM/YYYY")

Guide:

1. Create a new column in the Sales table.
2. Enter the above formula to format the OrderDate column to “DD/MM/YYYY”.

Conclusion

Understanding and mastering these functions can significantly enhance your ability to analyze data, create dynamic reports, and gain valuable insights.

By integrating these functions into your Power BI reports, you can create comprehensive, insightful, and visually appealing dashboards that meet complex business needs.

Whether you are a beginner or an experienced user, continuously exploring and applying DAX functions will enhance your analytical capabilities and decision-making process.

By mastering these DAX functions, you can perform complex calculations, create dynamic reports, and gain deep insights from your data in Power BI. Each function offers unique capabilities that, when combined, can handle a wide range of data analysis scenarios.

Related Article: What is Power Query?: Comprehensive Guide

References:

Here are some of the references that provide detailed information and additional resources on DAX functions in Power BI:

1. Microsoft Official Documentation

• DAX Overview – An overview of DAX with comprehensive documentation from Microsoft.
• DAX Function Reference – A detailed reference guide for all DAX functions, with examples and explanations.

2. SQLBI

• Introduction to DAX – A guide from SQLBI that provides in-depth tutorials and explanations on DAX functions.
• DAX Guide: A complete and interactive guide to DAX functions, with syntax, usage examples, and related functions.

3. Radacad

• DAX Basics in Power BI – An introductory tutorial on DAX basics, including common functions and practical examples.

• DAX Functions for Beginners – A list of essential DAX functions for beginners, with explanations and use cases.