Welcome to the world of DAX (Data Analysis Expressions), a powerful language used in Power BI, and other Microsoft tools for data modeling and analysis. Whether you’re a beginner or looking to enhance your DAX skills, this blog will introduce you to 25 essential Power BI DAX formulas and functions that will help you transform your data into insightful visualizations and reports.
Power BI DAX Overview
If you are not familiar with Power BI, please read What is Power BI before proceeding.
DAX is a collection of functions, operators, and constants that can be used in formulas or expressions to calculate and return one or more values. In simpler terms, DAX helps create new information from existing data in your data model.
It is designed to work with relational data and is an extremely important part of anyone working with Power BI.
Each model calculation type, calculated table, calculated column, or measure is defined by its name, followed by the equals symbol (=), which is then followed by a Power BI DAX formula. Use the following template to create a model calculation:
<Calculation name> = <DAX formula>
How Power BI DAX Differs from Excel Functions
While both DAX (Data Analysis Expressions) in Power BI and functions in Excel serve the purpose of data manipulation and analysis, there are key differences that set them apart:
- Context Awareness:
- DAX: Operates in both row and filter context, which allows for more complex calculations and aggregations based on the data model’s relationships and filters applied.
- Excel Functions: Generally operate in a cell-based context without an inherent understanding of data relationships or dynamic filtering.
- Data Modeling:
- DAX: Specifically designed for working with data models, allowing the creation of calculated columns, measures, and custom tables within Power BI’s model. It leverages relationships between tables to perform advanced analytics.
- Excel Functions: Primarily used within individual worksheets. While Excel has functionalities like PivotTables for data summarization, it doesn’t natively support complex data modeling to the extent that Power BI does.
- Performance:
- DAX: Optimized for large datasets and complex calculations. It’s built to handle vast amounts of data efficiently, making it suitable for enterprise-level data analysis.
- Excel Functions: Can become slow and cumbersome when dealing with large datasets or complex formulas, as it’s not primarily designed for large-scale data analytics.
- Syntax and Functions:
- DAX: Offers a range of specialized functions not available in Excel, such as CALCULATE, SUMX, and RELATED, which are essential for advanced data modeling and dynamic calculations.
- Excel Functions: Provides a broad array of functions for various purposes, but lacks some of the data model-specific functions found in DAX.
- User Interface:
- DAX in Power BI: Integrated within the Power BI environment, tailored for creating interactive reports and visualizations directly from the data model.
- Excel Functions: Utilized within the Excel spreadsheet interface, which is more suitable for direct data manipulation and simpler analyses.
In summary, while DAX and Excel functions share similarities in their foundational purpose, DAX’s advanced capabilities, context awareness, and integration with data models make it a more powerful tool for complex data analysis and business intelligence within Power BI. If you already know how to use functions in Excel, getting started with Power BI will be easier.
25 Powerful Power BI DAX Formulas and Functions for Beginners
Whether you’re new to Power BI or looking to deepen your understanding, these fundamental DAX (Data Analysis Expressions) tools will empower you to manipulate and analyze data with precision and efficiency.
- SUMX
The SUMX function is more powerful, allowing you to iterate over a table, evaluate an expression for each row, and then sum the results.
Syntax: SUMX(<table>, <expression>)
Parameters:
| Term | Definition |
| Table | The table containing the rows for evaluating the expression. |
| Expression | The expression to be evaluated for each row of the table. |
For example: Calculate the Total amount based on 2 information: Quantity & Selling price in the Sales table:
SUMX(‘Sales’,
‘Sales'[Quantity] * ‘Sales'[Selling price])
Practice at SUMX
- AVERAGEX
The AVERAGEX function, like SUMX, iterates over a table, evaluating an expression for each row before averaging the results.
Syntax: AVERAGEX(<table>,<expression>)
Parameters:
| Term | Definition |
| Table | Name of a table, or an expression that specifies the table over which the aggregation can be performed. |
| Expression | An expression with a scalar result, which will be evaluated for each row of the table in the first argument. |
For example: Calculate the average value of the Selling Price with Tax applied to each item:
AVERAGEX(‘Sale Data’,
‘Sale Data'[Gia ban] + ‘Sale Data'[VAT])
Practice at AVERAGEX
- COUNTX
COUNTX counts rows after evaluating an expression.
Syntax: COUNTAX(<table>,<expression>)
| Term | Definition |
| Table | The table containing the rows for which the expression will be evaluated. |
| Expression | The expression to be evaluated for each row of the table. |
For Example: Count all product lines that have a selling price definition:
COUNTX(‘Sale Data’, [Selling Price])
- COUNTROWS
The COUNTROWS function counts the number of rows in the specified table, or in a table defined by an expression.
Syntax: DISTINCTCOUNT(<column>)
Parameters:
| Term | Definition |
| table | (Optional) The name of the table that contains the rows to be counted, or an expression that returns a table. When not provided, the default value is the home table of the current expression. |
For example: Count the number of rows of the Sales table with the condition that only count the rows that declare the Date of Sale information:
COUNTROWS(
ALLNOBLANKROW(’Sale Datamaker'[Now]))
Practice at COUNTROWS
- COUNTBLANK()
COUNTBLANK counts the number of blank cells in a column.
Syntax: COUNTBLANK(<column>)
Parameters:
| Term | Definition |
| column | The column that contains the blank cells to be counted. |
For Example: Count the number of products in the Product column – count empty fields.
COUNTBLANK(‘Sale Data'[Product])
Practice at COUNTBLANK
- DISTINCTCOUNT
Syntax: DISTINCTCOUNT(<column>)
Parameters:
| Term | Definition |
| Column | The column that contains the values to be counted |
For example: Count the number of products in the Product column – do not count products with the same name.
DISTINCTCOUNT(‘Sale Data'[Product])
Practice at DISTINCOUNT
- SUMMARIZE
Returns a summary table for the requested totals over a set of groups.
Syntax: SUMMARIZE (<table>, <groupBy_columnName>[, <groupBy_columnName>]…[, <name>, <expression>]…)
Parameters:
| Term | Definition |
| Table | Any DAX expression that returns a table of data. |
| groupBy_ColumnName | (Optional) The qualified name of an existing column used to create summary groups based on the values found in it. This parameter cannot be an expression. |
| Name | The name given to a total or summarize column, enclosed in double quotes. |
| Expression | Any DAX expression that returns a single scalar value, where the expression is to be evaluated multiple times (for each row/context). |
For Example: Create a summarized table that shows the total revenue for each product.
SUMMARIZE (
Sales,
Sales[Product],
“Total Revenue”, SUM(Sales[Revenue])
)
Practice at SUMARIZE
- DIVIDE
Performs division and returns alternate result or BLANK() on division by 0.
Syntax: IF(<logical_test>, <value_if_true>[, <value_if_false>])
Parameters:
| Term | Definition |
| numerator | The dividend or number to divide. |
| denominator | The divisor or number to divide by. |
| alternateresult | (Optional) The value returned when division by zero results in an error. When not provided, the default value is BLANK(). |
For examples:
DIVIDE(5,2)
-> Returned result: 2.5
Practice at DIVIDE
- MIN
Returns the smallest value in a column, or between two scalar expressions.
Syntax:
- MIN(<column>)
- MIN(<expression1>, <expression2>)
Parameters:
| Term | Definition |
| column | The column in which you want to find the smallest value. |
| Expression | Any DAX expression that returns a single value. |
For example: Find the lowest revenue value:
MIN(‘Sale Datamaker'[Revenue])
Practice at MIN
- MINX
Returns the lowest value that results from evaluating an expression for each row of a table.
Syntax: MINX(<table>, < expression>,[<variant>])
Parameters:
| Term | Definition |
| table | The table containing the rows for which the expression will be evaluated. |
| expression | The expression to be evaluated for each row of the table. |
| variant | (Optional) If TRUE, and if there are variant or mixed value types, the lowest value based on ORDER BY ASC is returned. |
For example: Find the lowest profit value in 2017:
MINX(
FILTER(‘Sale Data'[Year] =2017),[Profit])
Practice at MINX
- MAX
Returns the largest value in a column, or between two scalar expressions.
Syntax:
- MAX(<column>)
- MAX(<expression1>, <expression2>)
Parameters:
| Term | Definition |
| column | The column in which you want to find the largest value. |
| Expression | Any DAX expression which returns a single value. |
For example: Find the highest revenue value:
MAX(‘Sale Data'[Revenue])
Practice at MAX
- MAXX
Returns the highest value that results from evaluating an expression for each row of a table.
Syntax: MAXX(<table>,<expression>,[<variant>])
Parameters:
| Term | Definition |
| table | The table containing the rows for which the expression will be evaluated. |
| expression | The expression to be evaluated for each row of the table. |
| variant | (Optional) If TRUE, and if there are variant or mixed value types, the highest value based on ORDER BY DESC is returned. |
For example: Find the highest profit value in 2017:
MAXX(
FILTER(‘Sale Data'[Year] =2017),[Profit])
Practice at MAXX
- FILTER
The FILTER function returns a table that has been filtered based on a Boolean expression.
Syntax: FILTER(<table>,<filter>)
Parameters:
| Term | Definition |
| Table | The table to be filtered. The table can also be an expression that results in a table. |
| Filter | A Boolean expression that is to be evaluated for each row of the table. For example, [Amount] > 0 or [Region] = “France” |
For example: Filter only rows where the revenue is greater than $1000
FILTER (
Sales,
Sales[Revenue] > 1000)
Practice at FILTER
- CALCULATE
The CALCULATE function changes the context in which data is evaluated.
Syntax: CALCULATE(<expression>[, <filter1> [, <filter2> [, …]]])
Parameters:
| Term | Definition |
| Expression | The expression to be evaluated. |
| filter1, filter2,… | (Optional) Boolean expressions or table expressions that defines filters, or filter modifier functions. |
For example: Calculate sales in the first 3 months of 2018:
CALCULATE(
SUM(‘Sale Datamaker’[Revenue]),
DATESBETWEEN(‘Sale Datamaker’[Today],
DATE(2018,1,1),
DATE(2018,3,31)))
Practice at CALCULATE
- ALLSELECTED
ALLSELECTED removes filters but respects the current context.
Syntax: ALLSELECTED([<tableName> | <columnName>[, <columnName>[, <columnName>[,…]]]] )
Parameters:
| Term | Definition |
| tableName | The name of an existing table, using standard DAX syntax. This parameter cannot be an expression. This parameter is optional. |
| columnName | The name of an existing column using standard DAX syntax, usually fully qualified. It cannot be an expression. This parameter is optional. |
For example: Calculate the total revenue for all products, ignoring any filters except for those applied by slicers or visuals in your report.
CALCULATE (
SUM(Sales[Revenue]),
ALLSELECTED(Sales) )
Practice at ALLSELECTED
- SELECTEDVALUE
Syntax: SELECTEDVALUE(<column>, [<alternateResult>])
| Term | Definition |
| column | The column from which to retrieve the single selected value. |
| alternateResult | An optional argument specifying the value to return when more than one value or no value is selected. |
For example: Display the selected product name based on user interaction with a slicer or filter.
SelectedProductName = SELECTEDVALUE(Products[ProductName])
Practice this DAX measure at SELECTEDVALUE
- SWITCH
Evaluates an expression against a list of values and returns one of multiple possible result expressions. This function can be used to avoid having multiple nested IF statements.
Syntax:
SalesCategory = SWITCH(<expression>, <value>, <result>[, <value>, <result>]…[, <else>])
Parameters
| Term | Definition |
| Expression | Any DAX expression that returns a single scalar value where the expression is to be evaluated multiple times (for each row/context). |
| Value | A constant value to be matched with the results of expression. |
| Result | Any scalar expression to be evaluated if the results of expression match the corresponding value. |
| Else | Any scalar expression to be evaluated if the result of expression doesn’t match any of the value arguments. |
For example: Add a column representing the name of the month corresponding to the specific month (by number):
SWITCH([Month],
1,”January”,
2,”February”,
3,”March”,
4,”April”,
5,”May”,
6,”June”,
7,”July”,
8,”August”,
9,”September”,
10,”October”,
11,”November”, 12,”December”
“Unknown value”)
Practice at SWITCH
- IF
IF checks a condition, and returns one value when it’s TRUE, otherwise it returns a second value.
Syntax: IF(<logical_test>, <value_if_true>[, <value_if_false>])
Parameters:
| Term | Definition |
| Logical_test | Any value or expression that can be evaluated to TRUE or FALSE. |
| Value_if_true | The value that’s returned if the logical test is TRUE. |
| Value_if_false | (Optional) The value that’s returned if the logical test is FALSE. If omitted, BLANK is returned. |
For example: Create a new column named SalesCategory that categorizes each row based on the revenue amount. If the revenue is greater than $1000, categorize it as “High”, otherwise categorize it as “Low”.
SalesCategory = IF ( Sales[Revenue] > 1000, “High”, “Low” )
Practice at IF
- VALUES
Converts a text string that represents a number to a number.
Syntax: VALUE(<text>)
Parameters:
| Term | Definition |
| Text | The text to be converted. |
For example: Knowing how many orders have been made:
COUNTROWS(
VALUES(‘Sale Data’[OrderNumber]))
Practice at VALUES
- RELATED
Returns a related value from another table.
Syntax: RELATED(<column>)
Parameters:
| Term | Definition |
| column | The column that contains the values you want to retrieve. |
For example: Filter transactions excluding product groups related to Microsoft Azure:
FILTER(‘Sale Data'[OrderID],
RELATED(‘Product Data'[Product]) <> “Microsoft Azure”)
Practice at RELATED
- RELATEDTABLE
Evaluates a table expression in a context modified by the given filters.
Syntax: RELATEDTABLE(<tableName>)
Parameters:
| Term | Definition |
| tableName | The name of an existing table using standard DAX syntax. It cannot be an expression. |
For example: Retrieve all products related to a specific order from the Sales table.
ProductsInOrder = RELATEDTABLE(Products)
Practice at RELATEDTABLE
- RANKX
The RANKX function ranks items in a table based on an expression.
Syntax: RANKX(<table>, <expression>[, <value>[, <order>[, <ties>]]])
Parameters:
| Term | Definition |
| Table | Any DAX expression that returns a table of data over which the expression is evaluated. |
| Expression | Any DAX expression that returns a single scalar value. The expression is evaluated for each row of table, to generate all possible values for ranking. See the remarks section to understand the function behavior when expression evaluates to BLANK. |
| Value | (Optional) Any DAX expression that returns a single scalar value whose rank is to be found. See the remarks section to understand the function’s behavior when value is not found in the expression. |
| Order | (Optional) A value that specifies how to rank value, low to high or high to low |
Practice at RANKX
- LOOKUPVALUE
The LOOKUPVALUE function searches a table and returns a value from a specified column.
Syntax:
LOOKUPVALUE (
<result_columnName>,
<search_columnName>,
<search_value>
[, <search2_columnName>, <search2_value>]…
[, <alternateResult>]
)
Parameters:
| Term | Definition |
| result_columnName | The name of an existing column that contains the value you want to return. It cannot be an expression. |
| search_columnName | The name of an existing column. It can be in the same table as result_columnName or in a related table. It cannot be an expression. Multiple pairs of search_columnName and search_value can be specified. |
| search_value | The value to search for in search_columnName. Multiple pairs of search_columnName and search_value can be specified. |
| alternateResult | (Optional) The value returned when the context for result_columnName has been filtered down to zero or more than one distinct value. If not specified, the function returns BLANK when result_columnName is filtered down to zero values or an error when there is more than one distinct value in the context for result_columnName. |
For example: Find products in the product group with the code “Microsoft Office 365”
LOOPUPVALUE(
‘Sale Datamaker'[Product],
‘Sale Datamaker'[Nhom product], “Microsoft Office 365”)
Practice at LOOKUPVALUE
- CONCATENATE
Concatenate joins two text strings into one text string.
Syntax: CONCATENATE(text1, [text2], …)
Parameters:
| Term | Definition |
| text1 | (required): The first item to join. The item can be a text value, number, or cell reference. |
| Text2, … | (optional): Additional text items to join. You can have up to 255 items, up to a total of 8,192 characters. |
For example: Concatenate the following 2 strings:
CONCATENATE(“Ha Noi”, “, ”, “Viet Nam”)
Return values: Ha Noi, Viet Nam
Alternatively, you can also use the & operator in DAX for concatenation, which is more concise:
= “Ha Noi” & “, ” &“Viet Nam”
Practice CONCATENATE
- TOTALYTD
Evaluates the year-to-date value of the expression in the current context.
Syntax: TOTALYTD(<expression>,<dates>[,<filter>][,<year_end_date>])
Parameters:
| Term | Definition |
| expression | An expression that returns a scalar value. |
| dates | A column that contains dates. |
| filter | (optional) An expression that specifies a filter to apply to the current context. |
| year_end_date | (optional) A literal string with a date that defines the year-end date. The default is December 31. |
For example: Create a measure named Total Revenue YTD that calculates the cumulative revenue year-to-date.
Total Revenue YTD = TOTALYTD ( SUM(Sales[Revenue]), Sales[Date])
Practice at TOTALYTD
DAX is a powerful language that enhances the capabilities of data modeling and analysis tools within the Microsoft ecosystem. Its ability to perform advanced calculations, create dynamic and interactive reports, handle time-based analysis, and improve performance makes it indispensable for data professionals. By mastering Power BI DAX, you can unlock the full potential of your data, providing valuable insights that drive informed business decisions.
