How to tackle DAX basics in Microsoft Power BI
Your email has been sent
Data Analysis Expressions, or DAX, are helpful resources for Power BI expressions. Learn how to use DAX now.
Learning how to use Power BI is fairly easy, and if you’re lucky, the built-in features will provide everything you need to produce professional dashboards for end users. However, you’ll get more out of the program if you learn Data Analysis Expressions. DAX is a collection of functions, operators and constants that you can use in expressions. It’s powerful, yet easy to learn.
SEE: Hiring kit: Microsoft Power BI developer (TechRepublic Premium)
In this tutorial, you’ll learn conceptual basics and syntax rules for writing DAX expressions. We’ll also work through a simple example. If you’re following along, I’m using Power BI Desktop on a Windows 10 64-bit system.
You can download the Microsoft Power BI demo file for this tutorial.
Jump to:
As a Power BI designer, you’ll want end users to get all the insights they need from what you create. Occasionally, that means using DAX, a set of functions that evaluate data to return meaningful information.
Most Power BI designers are familiar with writing expressions, and this skillset is especially helpful when learning DAX. If you’re familiar with Microsoft Excel functions, you’re in luck, because DAX and Excel functions are similar.
SEE: The Complete Microsoft Power BI Super Bundle (TechRepublic Academy)
Even if you have no experience with expressions, you can still learn to use DAX. The first step is to understand how to “speak” DAX. That requires learning a set of basic grammatical rules, or syntax.
In English, you might say: “I want to sum all of the sales values for the year 2021.” Power BI can’t interpret that request. To make DAX work for you, pretend that you’re learning a foreign language; in a sense, that’s exactly what you’re doing, just with a language of functions, arguments and Power BI context.
Functions are predefined instructions for evaluating values that you supply. There are several categories: Date and Time, Time Intelligence, Information, Logical, Mathematical, Statistical, Text, Parent/Child and more.
Although most functions return values, you can also return a table. Functions always reference a table or column, but you can apply filters to refer to specific rows.
Arguments allow you to pass specific values to the function for evaluation. Arguments can be another function, an expression, a column reference, a set of values or text, logical values such as TRUE and FALSE, and constants, giving context to the request.
Before we can move on to examples, we need to discuss context, which allows you to express whether you’re dealing with rows or filtered results, as follows:
Now you’re ready to put some of this newfound knowledge to work by writing a DAX expression in the form of a measure.
Power BI provides a number of implicit measures, which are predefined calculations in DAX. You’ll always want to check what’s available before writing your own to save yourself time and effort. When nothing’s available, you’ll need to write a model or explicit measure.
Why are we starting you out with a measure? Because it’s the easiest way to get started and, most likely, it’s the way you’ll solve most custom calculation needs.
Using the simple dataset shown in Figure A, let’s write a measure that returns a year-to-date value for each record. This simple .pbix file uses the Date table in the Fields pane as the Date table.
Figure A
To start, select the Sales table in the Fields pane and then click New Measure on the contextual Table Tools tab in the Calculations group. Power BI will enter Measure = in the resulting formula bar. Replace it with the following DAX expression, as shown in Figure B:Amount YTD = TOTALYTD(SUM('Sales'[Amount]), 'Date'[Date])
Figure B
Power BI adds a measure named Measure to the Sales table but doesn’t commit it just yet. Let’s look at this DAX expression by its individual elements:
Now, let’s look at the syntax so you know what’s expected for DAX to interpret and calculate the results you need.
The TOTALYTD function uses the following syntax:
TOTALYTD(<expression>,<dates>[,<filter>][,<year_end_date>])
As mentioned, TOTALYTD is a DAX function that evaluates year-to-date values of <expression> within the current (row) context. Now, let’s look at the arguments:
Now, let’s look at the DAX Sum function, which sums the values in a column using the syntaxSUM(<column>)
The only argument, column, identifies the column to total.
Now, let’s take a look at some other requirements:
With all of this new information in mind, can you expressAmount YTD = TOTALYTD(SUM('Sales'[Amount]), 'Date'[Date])
in English?
In English, this DAX expression says: “Return the sum of the Amount column in the Sales table, using the date values in the Date table’s Date column to determine the year.” In short, you’ve written an expression, using two DAX functions, that returns a running total by the year.
Now, you’re ready to commit the expression as a measure that you can add to a visualization. To do so, click the checkmark to the left of the formula bar. Doing so will update the name Measure in the Fields pane list to Amount YTD, as shown in Figure C. If you get an error, review the expression and correct any typos.
Figure C
Earlier, I mentioned that Date is a date table. If you’re not familiar with date tables, don’t worry. Power BI does a good job of handling dates internally. However, if you’d like to learn more, read the following articles:
To learn more about Power BI measures, you can read these articles:
Now, let’s see how the DAX expression written as a measure works.
Adding the measure doesn’t do much of anything. Power BI updates the name of the measure in the Fields pane, but you have to manually add it to a visualization to see how it works. Let’s do that now, using the simple Table visualization shown in the figures.
To do so, click the Table visualization to select it. Then, check the Amount YTD measure. Doing so adds it to the visualization, as you can see in Figure D.
Figure D
We know that the SUM function totals the values in the Sum of Amount column. That’s the name Power BI gives the Amount column when it’s used in a visualization because it’s a column that can be used in mathematical evaluations. You can change it in the visualization if you’d like, but the values you’re seeing are literal values from the Amount column for now. There’s no summing going on in that column.
The TOTALYTD’s first argument is the SUM function that’s summing the Amount column in the Sales table. The second argument tells Power BI to start a new total when the year in that column changes. Notice what happens between the two dates, 12/15/2021 and 2/17/2022. The latter date is in a new year, so the total starts over. That’s an internal instruction, so you don’t have to write any code to make it happen.
I have a surprise for you, though. You don’t have to write your own year-to-date expression, because Power BI has a quick measure that does the same thing. All you have to do is identify the arguments: The tables and columns.
One of the easiest ways to learn how to use DAX is to explore quick measures. Simply explore the interface (Table Tools tab) and enter several. When you see a function or syntax you don’t understand, you can use a search engine to learn more.
SEE: Quick glossary: Business intelligence and analytics (TechRepublic Premium)
Sometimes, there’s no other way to get the results you need without implementing DAX. Now that you know a bit about DAX, its syntax and a few of its functions, you’re ready to start writing expressions on your own.
Read next: Best business intelligence tools (TechRepublic)
Learn the latest news and best practices about data science, big data analytics, artificial intelligence, data security, and more. Delivered Mondays and Thursdays
Learn the latest news and best practices about data science, big data analytics, artificial intelligence, data security, and more. Delivered Mondays and Thursdays
How to tackle DAX basics in Microsoft Power BI
Your email has been sent
TechRepublic Premium content helps you solve your toughest IT issues and jump-start your career or next project.
This is a comprehensive list of the best AI art generators. Explore the advanced technology that transforms imagination into stunning artworks.
Find the perfect payroll service for your business without breaking the bank. Discover the top cheap payroll services, features, pricing and pros and cons.
Is NordVPN worth it? How much does it cost and is it safe to use? Read our NordVPN review to learn about pricing, features, security, and more.
Free project management software provides flexibility for managing projects without paying a cent. Check out our list of the top free project management tools.
Australian and New Zealand enterprises in the public cloud are facing pressure to optimize cloud strategies due to a growth in usage and expected future demand, including for artificial intelligence use cases.
The end of a year provides an ideal opportunity for revisiting the previous year’s goals and performance, reviewing personal and professional aspirations and prioritizing the upcoming year’s initiatives. But where to start? Tech pros can implement these seven planning steps from TechRepublic Premium to successfully set and track goals and subsequently prioritize initiatives. From the …
The role of a technical copywriter is recognized as a cornerstone in defining and conveying a company’s identity in the ever-evolving landscape of today’s business, where the online presence and narrative of a brand have outstanding value. This hiring kit from TechRepublic Premium provides an adjustable framework your business can use to find the right …
With artificial intelligence being more popular than free alcohol at a tech conference, it’s wise to stay informed about all things AI or even implement policies for its correct usage. This TechRepublic Premium pack provides readers with seven downloads for a bargain price. The bundle comprises two glossaries about AI and machine learning; three features …
A successful chief digital officer drives their organization’s digital transformation and creates value while rationalizing business processes and the customer experience. This hiring kit from TechRepublic Premium provides a workable framework you can use to find the best CDO for your organization. From the hiring kit: EDUCATION AND EXPERIENCE Candidates must have a degree in …
Get the web’s best business technology news, tutorials, reviews, trends, and analysis—in your inbox. Let’s start with the basics.
* – indicates required fields
Lost your password? Request a new password
Please enter your email adress. You will receive an email message with instructions on how to reset your password.
Check your email for a password reset link. If you didn’t receive an email don’t forgot to check your spam folder, otherwise contact support.
This will help us provide you with customized content.
Thanks for signing up! Keep an eye out for a confirmation email from our team. To ensure any newsletters you subscribed to hit your inbox, make sure to add [email protected] to your contacts list.
Leave a Reply