# Top 21 Common DAX Functions in Power BI: Ultimate guide

All the Key Points...

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).

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.