How to Write DAX Functions in Power BI

Learning DAX is the single biggest step you can take to level up your Power BI reports. It's the difference between creating simple, static charts and building dynamic, interactive analytical models that provide deep insights. This guide will walk you through the fundamentals of Data Analysis Expressions (DAX) to get you started on the right foot, even if you’ve never written a single formula before.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

What is DAX and Why Should You Care?

DAX, or Data Analysis Expressions, is a library of functions and operators used to build formulas in Power BI, Azure Analysis Services, and SQL Server Analysis Services. Don't let the name intimidate you. If you've ever written a formula in an Excel spreadsheet, like =SUM(A1:A10), you’ve already touched on the core concept of DAX.

Think of it as Excel formulas on steroids, designed specifically for data modeling and analysis. While Power BI's drag-and-drop interface is great for basic visualizations, DAX is what unlocks the tool's true power. You use it to perform custom calculations that go far beyond summing a column. For instance, you can use DAX to calculate:

• Year-over-year revenue growth
• Sales for the same period last year
• Running totals of inventory
• Customer lifetime value
• The percentage of total sales for a specific product category

Simply put, DAX gives you the ability to ask sophisticated questions about your data and display the answers in your reports.

The Building Blocks of DAX Syntax

Every DAX formula follows a specific structure or syntax. Understanding these fundamentals makes writing even the most complex expressions much more manageable. Let’s look at a simple example that creates a new calculation called "Total Sales":

Total Sales = SUM(Sales[Amount])

Here’s a breakdown of what each part does:

• Total Sales: This is the name of the new calculation, which in DAX is called a measure. We’ll cover measures in detail shortly.
• =: The equals sign is the assignment operator. It indicates that the formula that follows will define the measure named on the left.
• SUM: This is the DAX function being used. In this case, SUM adds up all the numbers in a column.
• (Sales[Amount]): This is the argument passed to the function. Functions always require one or more arguments enclosed in parentheses.

Learning this TableName[ColumnName] format is essential, as it's how you’ll reference data in your formulas.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Calculated Columns vs. Measures: What's the Difference?

One of the most common points of confusion for newcomers is the distinction between a calculated column and a measure. Both use DAX formulas, but they serve different purposes and behave very differently.

Calculated Columns

A calculated column is a new column that you physically add to one of your tables. The DAX formula for a calculated column is evaluated for each row in that table and an individual result is stored for every single row. You can see this new column in the Data View of Power BI.

When to use a calculated column:

• When you need a static value that is calculated based on other data in the same row.
• When you want to use the result as a filter, slicer, or axis in a visualization (e.g., categorizing customers into "High Value" and "Low Value").
• When you need to define something fixed for each row, like a profit margin or an order category.

Example: Creating a 'Profit' Column

Imagine an Orders table with columns for SalesAmount and ProductCost. You could create a Profit calculated column with this simple formula:

Profit = Orders[SalesAmount] - Orders[ProductCost]

Power BI would iterate through the Orders table, row by row, performing this calculation and storing the result in the new Profit column for each row.

Measures

A measure is a calculation that is performed on-the-fly, not stored row by row. Measures operate on aggregates of your data and their results are dynamic - they change based on the context of your report. For example, the result of a total sales measure will change depending on which year you've selected in a slicer or which product category you’re viewing in a table.

Measures are the backbone of most Power BI reports because they deliver the key performance indicators (KPIs) you need to analyze.

When to use a measure:

• When you need to calculate an aggregated value like a sum, average, percentage, or count.
• When the calculation depends on what the user selects in the report (e.g., date slicers, filters on product type).
• For virtually all numerical values you want to display in your charts and tables (e.g., Total Revenue, Average Order Value, Profit Margin %).

Example: Creating a 'Total Profit' Measure

Using the Profit calculated column we just made, we could create a measure to calculate the total profit across all orders:

Total Profit = SUM(Orders[Profit])

This measure doesn't store any data itself. When you drag it into a visual, Power BI calculates the SUM based on the current context.

Quick Reference: Column vs. Measure

As a general rule: if you want to calculate something for every single row and see the result in a new column, use a calculated column. For almost everything else, especially aggregations, use a measure.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Writing Your First DAX Measure: A Step-by-Step Guide

The best way to learn is by doing. Let's create a real-world measure from scratch. Suppose we have a Sales table with columns OrderQuantity and UnitPrice.

Our goal: Create a Total Revenue measure. We can't just sum UnitPrice or OrderQuantity independently, we need to multiply them together for each order and then sum the final amounts.

1. Create a New Measure: In the 'Report' view of Power BI, right-click on your Sales table in the 'Fields' pane on the right. Select "New measure". A formula bar will appear at the top of the report canvas where you can write your DAX formula.
2. Write the DAX Formula: We need to use what's called an "iterator" function: SUMX. This function iterates through a table, performs a calculation for each row, and then sums the results of those calculations.

Enter the following formula into the formula bar:

Total Revenue = SUMX(Sales, Sales[OrderQuantity] * Sales[UnitPrice])

This tells Power BI to go to the Sales table, and for each row, multiply the OrderQuantity by the UnitPrice. Then, after doing that for every single row, sum up all of those individual results.

1. Confirm and Format the Measure: Press 'Enter' to confirm the formula. You'll see your new 'Total Revenue' measure appear in the Sales table in the 'Fields' pane, marked with a calculator icon. With the measure selected, go to the "Measure tools" tab in the ribbon at the top. Here, you can change the format to 'Currency' and set the number of decimal places.
2. Use the Measure in a Visual: Now for the rewarding part! You can drag your new 'Total Revenue' measure onto a card visual to see the total, or into a table alongside a product category to see revenue broken down by category. The calculation will update dynamically based on where you use it.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

The Most Powerful Function in DAX: CALCULATE

If you take away just one advanced DAX function, make it CALCULATE. This function is the superpower of DAX because it allows you to modify the "filter context" of a calculation.

Essentially, CALCULATE lets you take an existing measure and apply additional filters on top of the ones already active in your visual. Its basic structure is:

CALCULATE(<expression>, <filter1>, <filter2>, ...)

For example, let's say you want to create a measure that only shows sales for a specific region, regardless of what's selected in the rest of the report. Imagine your Sales table is linked to a Products table that has a [Category] column.

A measure to calculate revenue just for "Accessories" would look like this:

Accessories Revenue = CALCULATE([Total Revenue], Products[Category] = "Accessories")

Now, even if you have product types filtered in your charts, this measure will always return the total revenue specifically from the "Accessories" category.

A more common use is with time-intelligence functions. Let’s create a Sales Last Year measure:

Sales Last Year = CALCULATE([Total Revenue], SAMEPERIODLASTYEAR('Date'[Date]))

Here, CALCULATE takes our existing Total Revenue measure and applies a new filter: SAMEPERIODLASTYEAR. This tells DAX to evaluate total revenue for the equivalent time period one year prior, letting you easily compare current and past performance.

Final Thoughts

Tackling DAX is a huge step forward in transforming your Power BI reports from simple summaries into powerful analysis hubs. By understanding the core differences between calculated columns and measures, and getting comfortable with key functions like SUMX and CALCULATE, you unlock the ability to get precise, custom answers from your data.

While a hands-on approach with tools like Power BI gives you complete control, setting all of this up comes with a substantial learning curve, especially for non-analysts. Sometimes you just need an answer fast, without getting tangled in formulas and data models. At Graphed, we've focused on eliminating that friction for marketing and sales teams by building an AI data analyst that allows you to just ask questions in plain English. Instead of writing DAX, you can simply connect with Graphed and ask "Show me my sales revenue by product category compared to last year," and get a live, interactive visualization instantly.