DAX is the formula language that lies at the heart of Power BI, the third head of Microsoft's Power Platform suite and that allows its users to create interactive reports and dashboards for monitoring business information. DAX makes it possible to transform raw data into useful information and greatly simplify the work of financial departments, data analysts and marketing managers. Understanding and using DAX effectively is critical to taking full advantage of Power BI's capabilities, and in this article, we're going to give a general overview of what it is, how it works, and what its practical applications are.
DAX, or Data Analysis Expressions, is the formula language that forms the core of the calculation and analysis capabilities of Power BI, Power Pivot and SQL Server Analysis Services (SSAS). DAX offers users who decide to use it a powerful and intuitive expression syntax that allows them to perform complex transformations and analysis of information within data models.
Similar to the formula language commonly used in Excel, DAX allows you to do calculations, add numbers, count objects and even make comparisons between different periods of time and transform the raw data collected into useful information for monitoring business performance and evaluating development scenarios for your business.
With Power BI and DAX, you can create charts and reports that not only show the data that interests us in a clean and orderly manner, but also help your company's analysts understand trends and make informed decisions, facilitating their visualization, study and comparison based on every possible useful parameter. Let's see together how.
First, if you're not familiar with the topic, let's take a moment to see what formula language is. It is nothing more than a specialized programming language designed for the creation and manipulation of mathematical, logical or analytical formulas within application software.
These languages are commonly used in spreadsheets, database management systems, data analysis applications, and other contexts where it is necessary to perform complex calculations or analysis on sets of information.
In Power BI, DAX is used for exactly that: to define and manage calculations within data models. These calculations can be used to create calculated measures and columns, which in turn can be used to build detailed, interactive visualizations. The language works directly with Power BI's tabular models, and this tabular architecture is similar to that of a relational database, but optimized for data analysis and visualization.
In DAX, measures and calculated columns are fundamental tools for analyzing data. So let's take a moment to take a closer look at what they are:
At the base of DAX is a query engine optimized to perform complex calculations efficiently. The DAX engine makes use of advanced data compression techniques and memory scanning algorithms, which allow calculations to be performed on millions of lines very quickly. This is made possible by VertiPaq, an in-memory compression engine, which is a central part of the Power BI architecture.
This approach allows data to be compressed much more effectively than traditional row storage formats, improving efficiency both in terms of memory and computing speed. When you execute a DAX formula, the query evaluation engine analyzes and optimizes the expression. The query is transformed into an efficient execution plan that minimizes the use of computational resources, and the engine uses techniques such as the generation of optimized SQL queries and intelligent cache management to improve performance.
DAX is compatible with existing data models and supports migration from other data analysis platforms. This means that organizations can easily transfer their data models and analysis to Power BI without losing functionality or having to rebuild everything from scratch.
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.
Now that we have a general idea of how DAX works within Power BI, it's time to take a closer look at the elements that make up and distinguish DAX to better understand how it works and how it can help us exercise our business practices.
To do this, we are going to talk in more detail about the calculation context, the different categories of predefined functions made available by the language and the operators that can be used for formulas. Let's see them together.
The calculation context is a central feature of DAX and determines which data is considered when calculating a formula. Basically, it is the set of conditions that define the subset of data on which a DAX formula operates.
There are two main types of calculation context that are applicable and they are respectively the Riga context And the filter context.
The Riga context refers to the current line when performing a calculation. It is especially relevant when using the RELATED or RELATEDTABLE function, which allows you to access values in related tables based on relationships defined in the data model. For example, when calculating a calculated column in a transaction table, the row context ensures that the formula considers the specific values of that row.
The filter context Instead, it applies to a subset of data filtered based on one or more criteria. This context can be explicitly defined within a formula using functions such as CALCULATE or FILTER, or it can be implicit, deriving from user interactions with Power BI reports, such as selecting a report-level filter or selecting a slice of a pie chart. Take for example the application of a filter to display only sales for a certain year, the filter context modifies all the measures calculated in the report to reflect only the data for that year.
DAX offers a wide range of predefined functions that make it easy to create complex calculations and detailed analyses. These functions can be classified into different categories, each with its own specific focus.
The first category that we are going to look at is that of aggregation functions such as SUM, AVERAGE, MIN and MAX allow you to perform aggregated calculations on columns of data. These functions are essential for summarizing large sets of data and are often used to create key measures such as total sales or average costs.
Then we have the time management functions, one of DAX's strengths, which allow data to be analyzed in the temporal context. Functions such as DATEADD, DATESYTD, and SAMEPERIODLASTYEAR allow comparative analysis over periods of time, such as comparing sales for this month with those of the same month last year.
Le logical functions such as IF, AND, and OR allow you to create conditional calculations that can return different values based on specific conditions. The IF function can be used, for example, to automatically assign a risk score to a customer based on items such as their purchase history.
Le string manipulation functions such as CONCATENATE, LEFT, RIGHT and LEN are instead essential for managing and analyzing textual data. These allow you to concatenate strings, extract substrings and calculate the length of strings, very useful in scenarios such as creating unique identifiers or cleaning input data.
Finally, we have the relationship functions such as RELATED and RELATEDTABLE that allow you to access values from related tables using the relationships defined in the data model. These functions are crucial when working with complex data models that include multiple related tables.
The operators in DAX are used to perform mathematical, logical and comparison operations in formulas and are divided into three main types, which are respectively those of arithmetic, comparison and logical operators.
Gli arithmetic operators such as +, -, * and/allow you to perform basic mathematical operations on numerical values. They are basically the classic symbols of mathematics that we are all somewhat familiar with.
Wanting to give an example of their use, let's take a formula like
Sales [Quantity] * Sales [UnitPrice]
The formula set up in this way simply calculates total sales by multiplying the quantity sold by the unit price.
Gli comparison operators such as =, >, <, >=, <=, and <> are instead used to compare values and determine if a given condition is true or false. Even these operators should be familiar to anyone with a school knowledge of mathematics and are essential for creating conditional calculations.
To give an idea of how they operate, let's take as an example the formula
IF (Sales [Amount] > 1000, “High”, “Low”)
With this syntax, the formula will assign a label of 'High' or 'Low' based on the calculated sales amount.
Finally, as a last type we have that of logical operators such as AND, OR, and NOT that combine or negate conditions, allowing you to create complex logic in formulas. These operators are often used in IF functions to evaluate multiple conditions.
For example, the formula
IF (AND (Sales [Region] = “North”, Sales [Amount] > 1000), “High”, “Low”)
returns the 'High' value only if the region is' North 'and the calculated sales amount exceeds the figure of 1000.
You can also use more advanced operators such as && and || to combine multiple conditions into a single logical expression.
To better understand how they work, let's consider the formula
IF (Sales [Amount] > 1000 && Sales [Category] = “Electronics”, “Premium”, “Standard”)
So set up, the formula classifies a sale as' Premium 'if the amount exceeds 1000 and the category is 'Electronics'.
Now that we are more aware of how DAX works, its characteristics and the elements that compose it, the time has come to see what its possible applications are within real scenarios.
It is never useless to emphasize how vitally important it is to monitor the performance of your team to identify areas for improvement, reward the best results and in general have the clearest and most complete overview possible of the state of your sales. Using DAX in Power BI, it is possible to create a dashboard that tracks all the key metrics we need, such as the number of sales concluded, their average value and the average time to close them.
To calculate the number of sales concluded, DAX allows you to sum the number of completed transactions for each team member, providing a clear overview of individual performance.
Sales Completed = COUNTROWS (FILTER (Sales, Sales [Status] = “Completed”))
The average sales value can be calculated using the AVERAGE function, which divides the total sales by the number of transactions, offering an indication of the quality of the sales made.
Average Sale Value = AVERAGE (Sales [Sale Amount])
For the average time needed to close a sale, DAX can add up the time taken for each transaction and divide them by the total number of sales, highlighting the team's efficiency.
Average Time to Close = AVERAGE (DATEDIFF (Sales [Start Date], Sales [Close Date], DAY))
With DAX, you can apply dynamic filters to examine performance over different periods, such as monthly or quarterly, or to compare performance across different regions or product categories. This level of detailed analysis helps managers quickly identify trends and take corrective action where necessary.
In the area of human resources, DAX can be used to analyze staff turnover and better understand the reasons behind employee resignation. A company can create a report that tracks the turnover rate, calculated by dividing the number of employees who have left the company by the total number of employees in a given period.
Turnover Rate = DIVIDE (COUNTROWS (FILTER (Employees, Employees [Status] = “Left”)), COUNTROWS (Employees))
Using DAX, it is possible to further segment this data by department, geographic location, or level of experience, providing a detailed view of turnover dynamics.
To identify factors that influence turnover, DAX can help to correlate resignation with variables such as job satisfaction, performance ratings, and compensation. Functions such as RELATED and SUMMARIZE can be used to aggregate and correlate data from different tables, offering a holistic view of the reasons that could lead employees to leave the company.
This formula, for example, aggregates the satisfaction scores of employees who have left the company, allowing human resources managers to better understand the causes of turnover and to implement strategies to improve retention.
Avg Satisfaction of Left Employees = AVERAGEX (FILTER (Employees, Employees [Status] = “Left”), RELATED (Feedback [Satisfaction Score]))
For financial offices, DAX in Power BI is an essential tool for all activities related to the management and optimization of the company budget. Finance managers can use DAX to create measures that monitor expenses against the allocated budget, allowing strict control of the company's finances.
If you want to give an example, it is possible to calculate the change in the budget by subtracting the actual expenses from the expected budget and then, dividing by the expected budget, obtaining the percentage change, allowing you to quickly identify the areas where the expenses exceed the budget.
Budget Variance = SUM (Budget [Planned Amount]) - SUM (Expenses [Actual Amount])
To obtain the percentage change compared to the budget, you can use the DIVIDE function to avoid errors in dividing by zero:
Budget Variance% = DIVIDE ([Budget Variance], SUM (Budget [Planned Amount]))
DAX can also be used to project future expenses based on historical trends. Using time intelligence functions such as TOTALYTD or DATESINPERIOD, financial managers can create forecasts that help plan future budget allocations. Below we provide an example of a formula that adds up all the expenses up to the current date considering the fiscal year:
Total Expenses YTD = TOTALYTD (SUM (Expenses [Amount]), Expenses [Date], “31/12")
In addition, with DAX, it is possible to segment expenses by category, department or project, allowing a detailed understanding of how financial resources are used and where improvements could be made. An example of possible syntax for the formula might be the following:
Total Expenses by Category = CALCULATE (SUM (Expenses [Amount]), ALLEXCEPT (Expenses, Expenses [Category])
In an office dedicated to customer service, the analysis of customer feedback is essential to improve the quality of the services offered by your business. Using DAX in Power BI, customer service managers can analyze customer ratings and comments to identify trends and areas for improvement.
DAX can be used to calculate the average rating of the service, adding up all the ratings received and dividing by the total number of responses. This measure provides a clear indication of overall customer satisfaction.
Average Customer Rating = AVERAGE (Feedback [Rating])
DAX also allows you to filter feedback by different dimensions, such as the type of service requested, the response time or the agent that handled the request. This allows you to identify specific areas of strength and weakness in customer service.
In addition, using functions such as COUNTROWS in combination with logical filters, it is possible to analyze the frequency of positive and negative comments and to correlate this data with other operational metrics, such as call handling time or resolution at the first interaction.
Let's say, for example, we want to count the number of positive comments. To do this, we can use a formula with this syntax:
Positive Feedback Count = COUNTROWS (FILTER (Feedback, Feedback [Rating] >= 4))
In project management, DAX in Power BI can be used to monitor performance and manage associated risks. Project managers can use DAX to create measures that track project progress”, calculated as the percentage of completed activities compared to the total number of planned activities.
Project Completion% = DIVIDE (COUNTROWS (FILTER (Tasks, Tasks [Status] = “Completed”)), COUNTROWS (Tasks))
DAX also allows you to analyze project costs and times in detail. Using functions such as SUM and DIVIDE, it is possible to calculate the cost per activity and the time per activity, identifying areas where projects are going beyond budget or expected time. An example would be:
Cost per Task = SUM (Tasks [Cost])
To assess any risks associated with your projects using DAX in Power BI, you can quickly create a formula that identifies critical activities that could cause delays or future problems for our business.
For this example, let's say we have a table called Projects that contains columns such as ProjectName, Activity, RiskLevel, PlannedEndDate, ActualEndDate, and Delay.
To help us identify critical issues, we can create a DAX measure that filters activities with high risk (RiskLevel = “High”), that are not yet completed by the scheduled date (ActualEndDate > PlanneDendDate), or that have a significant delay (for example, more than 7 days).
Critical Activities = CALCULATE (COUNTROWS (Projects), FILTER (Projects, Projects [RiskLevel] = “High” || Projects [ActualEndDate] > Projects [PlannedEndDate] || Projects [Delay] > 7)
To close our overview, we cannot fail to reiterate how DAX and Power BI are resources of immense value for companies that want to transform their data into strategic knowledge, in an era in which data is at the center of every successful project.
Thanks to the ability to perform complex calculations and extract detailed insights, DAX vastly amplifies the potential of Power BI, making it one of the best tools for analyzing and visualizing data currently on the market, and understanding and mastering it is no longer just a competitive advantage, but now a real necessity for navigating the complex contemporary business landscape.
DAX formulas are the key that allows you to create the dynamic and personalized reports that have made Power BI so appreciated and used within hundreds of thousands of companies. From monitoring business performance to forecasting future trends, DAX offers unique versatility that supports all analytical needs for every type of organization and department. Try it to believe it, then.
The Modern Work team effectively and swiftly addresses IT needs, primarily focusing on software development. The technical staff is well-trained in implementing software projects using Microsoft technology stacks and is skilled in managing both agile and long-term projects.
