This article is intended for those who are new to Power BI Desktop. It will explore the basic concepts of DAX in Power BI, showing how to apply them to common mathematical and data analysis tasks. We will delve into some theoretical concepts along the way: we will discover what the DAX formula is in Power BI and how to write it, what is a DAX function and what is context in DAX formulas.
Data Analysis Expressions (DAX) are specialized formulas used to analyze and calculate data. DAX powers all calculations into Power BI, providing a dynamic and versatile set of tools for creating new fields and tables within the data model. Although DAX is primarily associated with Power BI, it is also used in Power Pivot for Excel and in SQL Server Analysis Services (SSAS).
These expressions are comprised of various components, including functions, operators, and constants, which are combined into a single formula to produce a result (value or values). Power BI, and other business intelligence tools, effectively exploit the data at their disposal, mainly thanks to the power of DAX formulas in Power BI.
DAX is considered a 'functional language', meaning that every segment of code within it works like a function. This includes conditional statements, nested functions, references to values, and other elements, all of which can be incorporated into a DAX expression for execution.
DAX formulas can work with two main types of data: numeric and non-numeric. Numeric data types include integers, decimals, and currencies, while non-numeric data types consist of strings and binary objects. The evaluation of DAX expressions starts with the innermost function and proceeds outwards. Therefore, creating a well-organized DAX formula is crucial.
When a DAX formula is executed, it automatically converts the values of different data types to match the type for which it was designed. The DAX formula automatically castrates the resulting values to the desired data type.
DAX formulas are comprised of three fundamental components, and we'll cover each one in detail:
The DAX formulas in Power BI allow users to effectively exploit their data within Power BI, solving business challenges with precision. They facilitate simple calculations such as sums or averages and allow the creation of visualizations without requiring deep knowledge of DAX. For example, to generate a basic profit chart, simply drag the profit field to the Values section of the chart, where it automatically calculates the sum of the relevant rows.
Using a DAX formula becomes advantageous when you want to apply the same calculation in various contexts, such as on different charts or as part of other DAX expressions. This approach improves the efficiency of the report and simplifies future changes, as it is possible to make adjustments to a single formula instead of having to update multiple formulas distributed across different sections of the report.
If the business problem requires complex or customized calculations beyond the simple SUM or AVERAGE functions, the creation of DAX formulas becomes essential.
While a basic understanding of the Power BI interface allows you to effectively create and share online reports, understanding how to use DAX functions in Power BI is crucial for more complex calculations and dimensional analysis.
For example, you can calculate growth rates and visually represent them in different regions of a country to analyze data trends over time. DAX in Power BI allows designers to define new measures, helping companies identify problems and devise appropriate solutions.
Dev4Side Software specializes in creating customized reports through Microsoft Power BI, transforming complex data into intuitive dashboards and reports to support informed business decisions.
Operating vertically across the entire Microsoft Power Platform ecosystem, we have developed excellent expertise in Power BI, allowing us to offer business intelligence solutions that highlight critical insights and promote operational efficiency.
Find out how we can help you make the most of your data. Contact us to transform business information into concrete actions.
DAX formulas play a fundamental role in Power BI to create calculations in both calculated columns and measures. In addition, DAX is critical for implementing row-level security measures to protect data in reports and dashboards.
Decomposing a formula into its constituent elements facilitates a deeper understanding of any language. It is crucial to carefully study the syntax of these expressions to effectively create new formulas based on specific requirements.
To formulate calculated columns and measures, use the formula bar located at the top of the design model window or use the DAX Editor tool. To establish row-level security formulas, log in to the Role Manager or the Role Management dialog box. The information presented in this section is intended to provide you with a basic understanding of the DAX formulas in Power BI.
Understanding DAX formulas in Power BI is simple because of their intuitive and readable nature. This makes it quick to learn the basic concepts of DAX and start creating your own formulas. Let's explore the basic elements that make up proper DAX syntax:
Note that each subsequent parameter in a function is separated by a comma (“,”).
When you insert a formula into a calculated column and it is successfully validated, the column is instantly populated with the calculated values. In the case of measurements, pressing ENTER saves the definition of the measure together with the table. If there is an error in the formula, an error notification appears.
DAX functions in Power BI can be nested inside each other to perform multiple operations efficiently, which can greatly simplify the process of writing DAX formulas. For example, nesting multiple IF statements or using functions like IFERROR to encapsulate another function allows you to handle errors elegantly and customize error outputs as needed. This approach improves formula clarity and operational efficiency in Power BI and similar environments.
Some of the DAX functions commonly used in reports include:
Now let's take a practical example of a formula and break it down step by step. The formula in question is called 'Days in the current quarter':
Days in Current Quarter = COUNTROWS (DATESBETWEEN ('Date' [Date], STARTOFQUARTER (LASTDATE ('Date' [Date])), ENDOFQUARTER ('Date' [Date])))
This measure calculates a comparison ratio between a partial period and the previous period. The formula adapts to the portion of the period that has passed and compares it with the corresponding portion in the previous period. Specifically, divide [Days Current Quarter to Date] by [Days in Current Quarter] to determine the elapsed proportion of the current period.
A function in an expression is a formula defined with a specific name. Functions typically include mandatory and optional arguments, also called parameters, that serve as input. These parameters can be numeric values, constants, text strings, other functions or formulas, and logical values such as True or False. When a function is executed, it produces a value as a result. DAX includes a variety of functions designed to perform calculations involving dates and times, generate conditional values, manipulate strings, perform relationship-based searches, and iterate over tables to perform recursive calculations.
Although Excel formulas may seem familiar, DAX functions differ significantly in two key ways:
DAX includes several functions that return a table instead of a single value. These tables are generally not displayed directly in a reporting client, but serve as input for subsequent functions. For example, it is possible to retrieve a table and then calculate the count of distinct values within it, or dynamically sum the values through filtered tables or columns.
DAX functions include a range of temporal intelligence capabilities. These functions allow you to define or select date ranges and facilitate dynamic calculations based on these dates or intervals. For example, it is possible to compare sums between corresponding periods.
Let's take a look at some types of DAX functions in Power BI and some examples with their syntax.
Although the date and time functions in DAX are similar to those in Microsoft Excel, they operate using a datetime data type that begins on March 1, 1900. This distinction differentiates DAX functions in terms of data management and calculations involving dates and times. Here are some examples of a date and time function.
The start-date and end-date arguments can accept any DateTime value. The function returns a table consisting of a single column containing a sequence of dates within the specified range.
Syntax:
CALENDAR (<StartDate>,<EndDate>)
Example:
COUTDAYS (CALENDAR (DATE (2020,4,1), DATE (2020,6.5))) //returns 65
The DATEDIFF function calculates the difference between two dates based on the interval units specified by the user.
Syntax:
<Interval>DATEDIFF (<StartDate>,<EndDate>,)
Examples:
DATEDIFF (DATE (2020, 1.1), DATE (2020, 1:31), HOUR) //returns 720
DATEDIFF (DATE (2020, 1.1), DATE (2020, 3, 31), DAYS) //returns 90
DATEDIFF (DATE (2020, 1.1), DATE (2020, 4, 31), MONTH) //returns 3
The function returns the current value of the date and time in the standard format.
Syntax:
NOW ()
Example:
HOUR (NOW ()) //returns 12:00:00 AM
Converts the supplied date into a date-time text format.
Syntax:
<DateText>DATEVALUE ()
Examples:
DATEVALUE (“1/7/2020”) //returns
DATEVALUE (“20-3-2020”) //returns 1/20/3/2020 12:00:00 AM
DATEVALUE (“1-Jul-2020”) //returns 1/07/2020 12:00:00 AM
The DAX aggregation functions calculate a scalar value, such as a count, sum, average, minimum, or maximum, on all rows in a column or table as determined by the specified expression.
The time intelligence capabilities available in DAX allow the creation of calculations that take advantage of the built-in capabilities related to calendars and dates. Using time frames and dates together with aggregations or calculations, it is possible to build meaningful comparisons between equivalent time periods, such as sales trends and inventory analysis.
These functions are primarily used to calculate aggregations, manipulate data and develop Business Intelligence solutions using a date table as input.
The filter functions in DAX are designed to retrieve specific types of data, perform search operations between related tables, and filter data based on associated values. These search mechanisms operate in a similar way to database queries, taking advantage of table structures and defined relationships. Filter functions are crucial for data analysts, as they allow them to dynamically adjust the context of the data and facilitate complex calculations according to the requirements of the analysis.
DAX offers a series of financial functions designed to perform calculations such as net present value and rate of return. These functions reflect the financial instruments found in Microsoft Excel, providing robust capabilities for financial analysis within Power BI and other related environments.
In Power BI, logical functions evaluate expressions to provide information about the values within the expression. For example, the TRUE function determines if the evaluated expression produces a TRUE value. Some of these features include: DAX AND, DAX OR, DAX IF, DAX SWITCH.
An information function examines the specified cell or row and indicates if the value matches the expected type. For example, the ISERROR function returns TRUE when the reference value contains an error.
The mathematical functions in DAX are very similar to the mathematical and trigonometric functions in Excel, with slight variations in the types of numerical data. These functions are essential for performing a variety of calculations within Power BI.
The statistical functions in DAX calculate values associated with statistical distributions and probabilities, such as standard deviation and permutations. These functions are essential for executing DAX expressions within statistical models and aggregations.
These functions can generate a new table or modify existing tables. For example, the ADDCOLUMNS function allows you to add calculated columns to a designated table, while the SUMMARIZECOLUMNS function produces a summary table through specified groups.
DAX formulas in Power BI are responsive and adapt based on the source context. Understanding how the context works in DAX is crucial for resolving errors in formulas and ensuring accurate calculations.
DAX operates within two main contexts: the row context and the filter context. Familiarity with these contexts is essential to effectively manage relationships between data and optimize formula results.
This concept refers to the 'current row', which includes all the columns within a table and also extends to related tables. This context informs the DAX formula about the specific lines to include when executing a formula.
Let's take the following formula as an example:
Cost Price Per Unit = financials [COGS]/financials [Units Sold]
In calculating the Cost Price Per Unit, DAX operates on a line-by-line basis. This means that DAX must identify the current row as it iterates through the dataset, performing the calculation and populating the new column with the resulting values.
The row context is intrinsic to the calculated columns because the calculations are performed individually for each row, automatically establishing the row context. However, this is different for measures where aggregations apply to all rows in the table. In measures, there is no concept of a current row because calculations aggregate all rows collectively.
Now let's see an example of a DAX measurement formula:
Profit margin = SUM (financials [Profit])/SUM (financials [Sales])
In this case, a single figure is derived by adding the Profit column and then dividing by the sum of the Sales column. Because DAX performs aggregation, it doesn't require row-specific information, meaning that this measure has no row context.
To establish line context within a measure, you must use specialized functions known as iterators. Examples of these include SUMX, AVERAGEX, COUNTX. These iterators calculate the operations line by line and aggregate the results (such as sum, average, count, etc.). This approach explicitly defines the line context through the use of these iterator functions.
The following is an example of an iterator function:
Average Cost Per Unit = AVERAGEX (financials, financials [COGS]/financials [Units Sold])
In this example, two calculations are performed: first, the expression is evaluated line by line and then the result is used in the AVERAGE function. An alternative method to achieve the same result is to first create a calculated column called 'Cost Price Per Unit', as shown above, and then establish a separate measure of AVERAGE for that column. The effective use of iterator functions not only improves the efficiency of your reports, but also optimizes memory usage by allowing you to perform two calculations with a single formula.
The filter context integrates a row context by specifying a subset of rows or columns as filters within the report. These filters can be applied in a variety of ways:
An effective method for introducing a filter context into a DAX formula is the use of the CALCULATE function. This function allows you to add one or more filter parameters to a measure. For example, in the following example, let's create a profit margin measure filtered specifically for Italy:
ITA Profit Margin =
CALCULATE (SUM (financials [Profit])/SUM (financials [Sales]), financials [Country] = “Italy”)
In summary, DAX in Power BI stands out as a robust formula language that can handle data modeling, improve data insights, and effectively present measures within Power BI visualizations.
We've looked at what a DAX formula is in Power BI and its syntax, as well as DAX functions and some examples of them. We also discussed the importance of context in DAX formulas. Now, equipped with these fundamental DAX tools, we're only scratching the surface of what DAX can do.
Arming yourself with these basic DAX techniques means starting to discover the immense capabilities of this language. With a wealth of 250 features waiting to be explored, mastering DAX represents a formidable challenge and an extremely rewarding journey.
DAX (Data Analysis Expressions) is a formula language used in Power BI, Excel Power Pivot, and Analysis Services. It is designed to perform data analysis and calculations on data models, allowing users to create new information from existing data.
DAX enhances Power BI by enabling users to create custom calculations and aggregations, build complex measures, and create calculated columns. This functionality allows for deeper insights and more sophisticated data analysis within reports and dashboards.
Some key features of DAX include:
Yes, DAX can be used with other Microsoft products such as Excel Power Pivot and SQL Server Analysis Services (SSAS). This allows for a consistent approach to data analysis across different tools and platforms.
Some common DAX functions include:
Learning DAX effectively involves:
Some best practices for using DAX in Power BI include:
The Modern Apps team specializes in development and integration across the entire Microsoft 365 ecosystem. We design native applications for Microsoft and Azure platforms, and implement business processes that integrate with and maximize the investment in Microsoft 365.
