DAX is the language of Microsoft Power BI. It lets you create calculated columns and measures to make your data work for you.
This article takes a detailed look at DAX, explains how it works, and shows some examples of what you can do with it. We also cover the different ways DAX can help you analyze your data.
Whether you’re just starting with Power BI or already familiar with DAX, this article has valuable information for everyone.
What is DAX?
Data Analysis Expressions (DAX) is a powerful formula language designed specifically for data modeling in Power BI. DAX formulas calculate measures and perform analysis throughout Power BI, often used to avoid complex calculations and manual workarounds in analytical models.
With DAX, users can create custom calculations involving mathematical and statistical formulas, time intelligence functions, and other operators. Users can also utilize DAX to enhance data models with calculated columns, which are new columns generated by DAX formulas based on existing columns in the data model.
DAX and Power Query are often confused. Let’s go over some key differences. Power Query and DAX are both tools for performing data analysis. Power Query is a self-service business intelligence (BI) tool to fetch and transform data. It’s user-friendly and available to nontechnical users.
DAX, on the other hand, is a more technical tool designed for use by analysts and developers. It can create custom calculations and measures. While Power Query can perform many of the same tasks as DAX, it’s not as powerful or flexible. Consequently, DAX is often the preferred choice for complex data analysis services.
DAX functions perform calculations on data already in your Power BI data set. This is different from Microsoft Excel, which can perform calculations only on data imported into the spreadsheet.
In addition, DAX can create dynamic filters that change the filter context for other visuals on a report page based on user interactions with visuals on the page.
The DAX language is continuously evolving, with new functions and features added regularly. As a result, Power BI users proficient in DAX can always find new ways to solve complex problems and perform sophisticated analyses.
Common DAX use cases in Power BI
DAX is a language used to create custom calculations in Power BI. DAX includes a wide variety of functions to perform many different types of data analysis.
Some common use cases for DAX include:
- Calculating percentages and totals. Use DAX to calculate things like the percentage of total sales or the total number of customers.
- Creating custom aggregations. Use DAX to create custom aggregations, like the average customer spending over time.
- Filtering and sorting data. Use DAX to filter and sort data in Power BI based on specific criteria.
- Understanding customer data to drive marketing. Use data to understand customer needs and preferences and make key marketing available to your team.
If you’re new to DAX, here are a few things you should know.
- DAX is a formula language, meaning all DAX formulas must start with an equal sign (=).
- DAX formulas are case-sensitive; use the correct case when writing your formulas.
- DAX includes many of the same functions available in Excel. Those familiar with Microsoft Excel will find learning DAX fairly easy.
- Power BI has a built-in function library you can use to help write DAX formulas. This function library includes helpful information about each function and examples of how to use each function.
Expressions in DAX
Expressions in DAX are used to perform logical operations on data. You can use DAX expressions to compare values, filter data, and calculate results.
If you’re looking for more general information on DAX, Power BI, Power BI desktop, and more, Microsoft provides a tutorial that covers the following topics:
- An overview of logical functions in DAX
- How to use DAX expressions to compare values
- How to use DAX expressions to filter data
- How to calculate results using DAX expressions
Calculations in DAX
You can use DAX to perform a variety of calculations on data sets. You can also use data types in DAX calculations.
The two types of calculations are calculated columns and calculated measures. Calculated columns are based on a single column of data, while calculated measures are based on multiple columns of data.
Calculated columns create a new column in your existing table. The only difference between a regular column and a calculated column is that it’s necessary to have at least one function in the calculated column. Use these when creating a column with filtered or sorted information.
Calculated measures, on the other hand, create a field with aggregated values like sums, ratios, percentages, averages, etc.
Most common DAX functions
DAX is the native language of Power BI and is used to perform data analysis tasks, such as calculating new columns in tables and creating measures to summarize data. You can use DAX to power pivot data in Excel, providing a way to manipulate and analyze data that’s not possible with traditional Excel formulas.
DAX includes a variety of functions to perform mathematical calculations, manipulate text values, work with dates and time values, and more. DAX has a specific syntax that must be followed to calculate values correctly. This syntax is different from traditional Excel formulas.
Microsoft’s DAX function reference provides detailed information on syntax, parameters, return values, and examples for each of the over 250 functions used in DAX formulas. That’s a lot of functions on Microsoft Power BI!
The DAX syntax includes a number of operators and functions to calculate values. Let’s go over some of the major functions in more detail.
Aggregation functions
Many aggregate functions are already available in DAX, and the list continues to grow with new releases of Power BI. These functions allow you to perform mathematical operations on your data set.
The aggregate function you use will depend on the type of data you’re working with and the results you’re trying to achieve.
It’s important to keep in mind the data type of the values you’re working with when using aggregate functions. Aggregate functions will return a different data type depending on the data type of the values that you are aggregating. When using aggregate functions, you can also specify an optional filter expression to further refine the results of the aggregate calculation.
Let’s explore several aggregate functions in more detail.
- AVERAGE. The AVERAGE function takes an arithmetic mean of the values in the column for data analytics.
- MAX. MAX returns the largest value in a column or between two scalar expressions for Power BI reports.
- MIN. The MIN function takes a column or two expressions as an argument and returns the smallest value. The following types of values in the columns are counted:
- Numbers
- Texts
- Dates
- Blanks
- SUM. Sum and SUMX both do the same thing: aggregate data! It can be difficult to tell the difference, but it ultimately depends on the data set you need to generate. The SUM function is a simple aggregation function. It summarizes a value based on a filter context.
- SUMX. SUMX is the sum of an expression, the X at the end of this function is for eXpression. SUMX is an iterator function used in different instances of data science. However, you need to specify a table name for SUMX to work. When using SUM, you don’t need a table name because one column only belongs to one table. But when using SUMX, you may write an expression that uses columns from other tables.
Counting functions
Counting functions in Power BI are used to count the number of items in a column or table. There are many counting functions, but the most commonly used are COUNT, COUNTA, and COUNTBLANK.
These functions are useful for performing calculations on data. For example, you could use the COUNT function to count the number of rows in a table or the COUNTA function to count the number of non-empty cells in a column.
- COUNT. COUNT counts the number of cells that contain numbers, dates, or strings. This function is often used to count the number of rows in a table.
- COUNTA. COUNTA counts the number of cells that are not blank. This function is often used to count the number of non-empty cells in a column.
- COUNTBLANK. COUNTBLANK counts the number of blank cells. This function is often used to count the number of empty cells in a column.
- COUNTROWS. This function counts the number of rows in a base table, but more often is used to count the number of rows that result from filtering a table or applying context to a table. The return value for this Power BI DAX function is a whole number and depends on row context.
- DISTINCTCOUNT. DISTINCTCOUNT returns the number of distinct values in a column. The only argument allowed to this function is a column. You can use columns containing any type of data. When the function finds no rows to count, it returns a BLANK; otherwise, it returns the count of distinct values.
Logical functions
Logical functions in Power BI are used for testing conditions and returning a result. The most commonly used logic functions are IF, AND, and OR.
- AND. AND tests multiple conditions and returns TRUE if all conditions are true or FALSE if any condition is false.
- OR. The OR logic function checks whether one of the arguments is true to return TRUE. The function returns FALSE if both arguments are false.
- NOT. The NOT function changes FALSE to TRUE or TRUE to FALSE.
- IF. IF tests a condition and returns one value if the condition is true and another value if the condition is false.
- IFERROR. IFERROR evaluates an expression and returns a specified value if the expression returns an error; otherwise, it returns the value of the expression itself.
Information functions
You can use DAX to create custom visuals and reports. It’s also a great way to perform calculations on data. Information functions in Power BI are used to retrieve information about data, objects, and users. The most commonly used information functions are USERNAME and USERPRINCIPALNAME.
USERNAME returns the name of the current user. This function is often used to return the name of the person logged into Power BI.
USERPRINCIPALNAME returns the user principal name of the current user. This function is often used to return the email address of the person logged in to Power BI.
Here are some other common information functions:
- ISBLANK. ISBLANK is a DAX function that checks whether a value is blank and returns TRUE or FALSE.
- ISNUMBER. ISNUMBER checks whether a value is a number and returns TRUE or FALSE. It will return TRUE if the value is numeric; otherwise, FALSE.
- ISTEXT. ISTEXT checks if a value is text and returns TRUE or FALSE.
- ISNONTEXT. ISNONTEXT checks if a value is not text (blank cells are not text) and returns TRUE or FALSE.
- ISERROR. ISERROR is used to check for errors. This information function checks whether a value is an error and returns TRUE or FALSE.
Text functions
Text functions are used for manipulating text data. The most commonly used text functions are LEFT, RIGHT, and LEN.
- LEFT returns the leftmost characters of a string. This function is often used to return the first name from a full name.
- RIGHT returns the rightmost characters of a string. This function is often used to return the last name from a full name.
- LEN returns the length of a string. This function is often used to count the number of characters in a string.
Some other common text functions used in Power BI:
- CONCATENATE. This text function joins two text strings into one concentrated text string.
- REPLACE. REPLACE is very useful for correcting text errors or updating the text. REPLACE replaces part of a text string, based on the number of characters you specify, with a different text string.
- SEARCH. Search is essential in data science. The SEARCH function returns the number of the character at which a specific character or text string is first found, reading left to right. Search is case insensitive and accent sensitive.
- UPPER. While this Power BI text isn’t used often, it still performs an important DAX function. UPPER converts a text string to all uppercase letters.
- FIXED. FIXED can help make data models and data analysis expressions easier to read by rounding numbers with decimals and returning them as text. FIXED rounds a number to the specified number of decimals and returns the result as text. You can specify that the result is returned with or without commas.
Date functions
DAX date functions are used for manipulating date data. The most commonly used DAX date functions are TODAY and NOW.
- TODAY returns the current date. This function is often used to return the date on which a report was generated.
- NOW returns the current date and time. This function is often used to return the date and time on which a report was generated.
These functions are very useful for manipulating date data sets.
- DATE. DATE returns the specified date in DateTime format. Parameters include year, month, and day. DATE takes the integers that are input as arguments and generates the corresponding date. The DATE function is most useful in situations where the year, month, and day are supplied by formulas or data sets that may not be read as dates. For example, the underlying data might contain dates in a format not recognized as a date, such as YYYYMMDD. You can use the DATE function in conjunction with other functions to convert the dates to a number that can be recognized as a date.
- HOUR. HOUR is a DAX function that returns the hour as a number from 0 (12 a.m.) to 23 (11 p.m.).
- NOW. Returns the current date and time in DateTime format. The NOW function is useful when you need to display the current date and time on a worksheet or calculate a value based on the current date and time and have that value updated each time you open the worksheet.
- EOMONTH. The only constant regarding DAX functions is how endlessly specific they can be. The EOMONTH function returns the date in DateTime format of the last day of the month, before or after a specified number of months. Use EOMONTH to calculate maturity dates or due dates that fall on the last day of the month—a useful tool when it’s a data set important to your business intelligence.
- WEEKDAY. WEEKDAY returns a number from 1 to 7, identifying the day of the week of a date. By default, the day ranges from 1 (Sunday) to 7 (Saturday).
Work as a Power BI consultant by browsing available jobs on Upwork today. With hundreds of jobs posted, you choose when and how you work.
Advanced functions
Advanced functions in Microsoft Power BI are used to perform more complex calculations on data. Advanced functions include:
- Filter functions
- Financial functions
- Math and trig functions
- Parent and child functions
- Statistical functions
- Table manipulation functions
- Time intelligence functions
How to add DAX formulas in Power BI
Below are three ways to add DAX formulas within Power Bi.
- Use the Add Column button in the Modeling ribbon to create a custom column. This will open the Formula Bar, where you can enter your DAX formula.
- You can also add DAX formulas to measures. To do this, open the Measure pane and click on the New Measure button. This will open the Formula Bar to enter your DAX formula.
- Finally, you can add DAX formulas to conditional formatting rules. To do this, go to the Formatting pane and select the conditional formatting rule you want to edit. In the Edit Formatting Rule dialog box, go to the Value field and click on the Fx icon. This will open the EditExpression dialog box, where you can enter your DAX formula.
Why you should use DAX in Power BI
While DAX may seem daunting at first, its flexibility and power make it an essential tool for any Power BI user. By mastering DAX, you can unlock the full potential of Power BI and create truly insightful data visualizations.
Using DAX in Power BI is recommended for anyone who wants to perform sophisticated data analysis. With DAX, you can go beyond simple visualization and uncover hidden insights in your data. For instance, DAX can help you create smart calculated columns to speed up analysis performance and make data more simple, elegant, and easy to read.
Dynamic columns, tables, and measures also help you improve your way of viewing, understanding, and thinking about how to efficiently filter, merge, and manipulate data.
The job of a Power BI specialist
As a Power BI specialist, you’re responsible for designing and creating robust, visually appealing business intelligence (BI) solutions. You work closely with clients to understand their needs and objectives and translate those into actionable insights using Power BI tools.
Power BI specialists are in high demand due to the ever-growing reliance on data analytics. As a Power BI specialist, you’ll be responsible for developing and maintaining Power BI dashboards and reports for organizations.
You may also be involved in data analysis and modeling, as well as developing custom applications on the Power BI platform. A Power BI specialist with the right skill set can pursue a variety of exciting career options.
In the world of business intelligence, data analysis is king. And when it comes to analyzing data, few tools are as powerful as DAX. If you’re a Power BI specialist, Upwork is here to help you land your perfect project assignment. Our platform connects businesses with independent professionals from around the globe, making it easy for you to find work that fits your skills and schedule.
