Power BI Introduction to DAX Formulas

DAX stands for Data Analysis Expressions. It is the formula language used in Power BI to create custom calculations. DAX lets you go beyond the simple sum or count that Power BI performs automatically — you can build formulas that calculate profit margins, running totals, year-over-year growth, or customer counts based on specific conditions.

Why You Need DAX

When you drag a Sales Amount column into a visual, Power BI automatically sums all the values. That gives you total sales. But what if you need to calculate the profit margin as a percentage? Or find how many customers placed more than one order? Or compare this month's revenue to the same month last year?

None of these results exist as a column in your raw data. You calculate them using DAX formulas. DAX transforms your data model from a storage container into a calculation engine.

Think of DAX like the formulas in a spreadsheet, but far more powerful. A spreadsheet formula like =SUM(A1:A10) adds up a fixed range of cells. A DAX formula understands the context of your entire data model — it knows which product, which region, which time period is currently selected — and calculates results accordingly.

Where You Write DAX

You write DAX in two places in Power BI Desktop:

New Measure

Go to the Home tab or the Modeling tab and click New Measure. A formula bar appears at the top of the screen where you type your DAX formula. Measures are the most common use of DAX — they calculate results dynamically based on the filters and context in your report.

New Column

Also in the Modeling tab, click New Column. This lets you write a DAX formula that adds a new column to a table. Calculated columns compute a value for each row of the table when the data loads.

DAX Formula Structure

Every DAX formula has the same basic structure:

Name = Expression

The name is what you want to call your calculation. The equals sign separates the name from the formula. The expression is the actual DAX calculation.

A simple example:

Total Sales = SUM(Sales[Revenue])

Here, "Total Sales" is the name. SUM is the DAX function being used. Sales is the table name. Revenue is the column name inside square brackets.

Another example:

Profit Margin % = DIVIDE(SUM(Sales[Profit]), SUM(Sales[Revenue]), 0)

This formula divides total profit by total revenue to get a percentage. The third argument (0) is what to show if revenue is zero — it prevents division-by-zero errors.

Understanding Evaluation Context

The most important concept in DAX is context — specifically, filter context. A DAX measure does not calculate a single fixed number. It calculates a result based on whatever filters are currently active in the report.

Imagine you have a measure called Total Sales = SUM(Sales[Revenue]). When you use this in a bar chart showing sales by region, the measure calculates total sales for each region separately. When a user clicks on "North" in a slicer, the measure recalculates to show only North region sales.

The same measure adapts to its surroundings automatically. This is the power of context. DAX always asks: "What is active right now? What filters are selected? Calculate the result within that context."

Row Context vs Filter Context

Row Context

When a DAX formula runs in a calculated column, it processes each row of a table one at a time. As it processes each row, it knows which row it is currently on. This is called row context. For example, a calculated column formula like [Price] * [Quantity] multiplies the price and quantity from the same row — it knows which row it is working on.

Filter Context

When a DAX measure runs inside a visual, it sees the filters currently applied to the report — the selected year, the selected region, the selected product category. These filters collectively form the filter context. The measure calculates its result only for the data that passes through these filters.

Common Beginner DAX Functions

SUM

Adds all values in a column. SUM(Sales[Revenue]) returns the total of all revenue values in the Revenue column of the Sales table.

COUNT

Counts the number of rows that contain a number in a column. COUNT(Sales[Order ID]) counts how many order IDs exist.

COUNTA

Counts rows that contain any non-blank value (works with text and numbers). Useful when counting rows with text IDs or names.

AVERAGE

Returns the average of all values in a column. AVERAGE(Sales[Revenue]) gives the average revenue per transaction.

MIN and MAX

Return the smallest and largest values in a column respectively. Useful for finding the cheapest product, oldest date, or highest individual sale.

DIVIDE

Divides one number by another safely. The third argument specifies what to return if the denominator is zero — prevents errors from crashing your visuals. Always use DIVIDE instead of the / symbol for division in DAX.

DAX Is Case-Insensitive but Syntax-Sensitive

Writing SUM or sum or Sum all work the same way in DAX — capitalization of function names does not matter. However, table names and column names must match exactly. If your table is named "Sales Data" (with a space), you must reference it as 'Sales Data'[Revenue] — with single quotes around the table name because of the space.

Power BI's formula bar helps by offering autocomplete suggestions as you type. Start typing a function name or table name and a dropdown appears showing matching options. Selecting from this dropdown ensures correct spelling and syntax.

Key Points

• DAX is the formula language in Power BI used to create custom calculations beyond basic aggregations.
• Every DAX formula follows the structure: Name = Expression.
• Measures calculate dynamically based on the current filter context in the report.
• Calculated columns process row by row using row context.
• Start with SUM, COUNT, AVERAGE, MIN, MAX, and DIVIDE — these cover most beginner calculation needs.
• Use DIVIDE instead of the division slash to avoid division-by-zero errors.