What Is DAX? The Power BI Formulas Data Analysts Learn First

DAX (Data Analysis Expressions) is the formula language behind Power BI. Analysts use it to create measures, calculated columns and calculated tables on top of a data model, so a report can answer questions like "What were sales this month compared with the same month last year?" without rewriting the source data. If you already write Excel formulas, DAX will look familiar at first; the difference is that DAX works on tables and relationships, and its results change with the filters a report user applies.

This guide explains what DAX is, the handful of concepts that make it click, and the first functions worth learning if you are building your first Power BI reports.

What DAX is and where it runs

Microsoft describes DAX as a collection of functions, operators and constants that can be combined into formulas to calculate and return one or more values. You will find it in three Microsoft products: Power BI, Analysis Services tabular models and Power Pivot in Excel. The official DAX overview on Microsoft Learn is the reference you will return to most often.

DAX is not a data-cleaning language. Shaping and cleaning raw data happens earlier, usually in Power Query (the same engine covered in our guide to Power Query in Excel). DAX comes in once the tables are loaded and related, and its job is to calculate.

DAX vs Excel formulas vs SQL

• Excel formulas reference cells and ranges. DAX references whole columns and tables, never individual cells.

• SQL queries a database and returns a result set. DAX measures are calculated on demand inside the report, and they recalculate every time a slicer, filter or visual changes.

• Syntax feels like Excel (functions such as SUM, IF and DIVIDE), but the logic feels closer to SQL aggregations with a GROUP BY that the report decides for you.

Measures vs calculated columns

This is the first decision every beginner faces, and getting it wrong is the most common reason Power BI files become slow and confusing.

• Calculated column: computed row by row when the data is refreshed and stored in the model. Use it when you need a value per row that you will slice or filter by, such as a "Price band" label or a fiscal-quarter column.

• Measure: computed at query time based on whatever filters are active. Use it for anything you aggregate: total revenue, average order value, conversion rate, year-over-year growth.

A useful rule: if the number should change when someone clicks a filter, it is almost always a measure. Microsoft's tutorial on creating measures in Power BI Desktop walks through the basics step by step.

The two contexts that make DAX click

Most DAX confusion comes from not knowing which "context" a formula is evaluated in.

Row context

Row context means "the current row". It exists inside calculated columns and inside iterator functions like SUMX. When you write Sales[Quantity] * Sales[Unit Price] in a calculated column, DAX evaluates it once for each row.

Filter context

Filter context is the set of filters applied when a measure is evaluated: the slicer selections, the row and column of a matrix, the filters on the page. The same measure Total Sales = SUM(Sales[Amount]) returns a different number in every cell of a matrix because each cell has a different filter context.

Once you see that a measure is just "an aggregation evaluated under the current filters", most of DAX becomes predictable.

CALCULATE: the function that changes filters

CALCULATE evaluates an expression under a modified filter context. It is the most important function in the language, and Microsoft's CALCULATE reference is worth reading twice.

Example: Online Sales = CALCULATE([Total Sales], Sales[Channel] = "Online") returns total sales for the online channel only, regardless of the channel filter on the page. With CALCULATE you can build shares of total, filtered KPIs and comparisons between segments.

The first 10 DAX functions to learn

1. SUM, AVERAGE, MIN, MAX: basic aggregations over a column.

2. COUNTROWS: counts rows in a table, ideal for "number of orders".

3. DISTINCTCOUNT: unique customers, unique products.

4. DIVIDE: safe division that handles divide-by-zero, perfect for rates and ratios.

5. CALCULATE: changes filter context.

6. FILTER: returns a filtered table to use inside CALCULATE or an iterator.

7. ALL / REMOVEFILTERS: ignore filters, used for "% of total".

8. SUMX: iterates row by row, for example revenue as quantity times price.

9. IF and SWITCH: conditional logic for labels and thresholds.

10. TOTALYTD and SAMEPERIODLASTYEAR: time intelligence for year-to-date and year-over-year comparisons, which require a proper date table.

A simple example: year-over-year growth

Three measures build on each other:

• Total Sales = SUM(Sales[Amount])

• Sales LY = CALCULATE([Total Sales], SAMEPERIODLASTYEAR('Date'[Date]))

• YoY Growth % = DIVIDE([Total Sales] - [Sales LY], [Sales LY])

Put YoY Growth % in a matrix by month and region and it recalculates for every cell. That is the payoff of DAX: you write the logic once and the report applies it everywhere. Before choosing which numbers deserve a measure, it helps to define them clearly, as covered in our guide to KPI examples for data analysts.

Common beginner mistakes

• Using calculated columns for everything. They bloat the model and do not respond to filters the way measures do.

• Skipping the data model. DAX works best on a star schema with fact and dimension tables. Microsoft's star schema guidance for Power BI explains why.

• No dedicated date table. Time intelligence functions expect a continuous date table marked as such.

• Using the "/" operator for ratios. DIVIDE avoids errors when the denominator is zero or blank.

• Not naming measures clearly. "Total Sales" and "Sales LY" are easier to reuse than "Measure 3".

Do you need DAX to become a data analyst?

It depends on the stack. If the companies you target report in Power BI, DAX is a core skill and shows up in interviews. If they use Tableau, Looker or notebooks, you will spend more time in SQL and Python. Either way, the thinking behind DAX (clear metric definitions, a clean data model, aggregations that respect filters) transfers to every BI tool. If you are still deciding where to start, our comparison of Excel vs Power BI can help.

FAQ

Is DAX hard to learn?

The basic aggregations take an afternoon. Filter context and CALCULATE take a few weeks of practice on real reports before they feel natural.

Is DAX the same as Power Query M?

No. M is the language of Power Query, used to import and transform data. DAX calculates on the loaded model.

Can I use AI assistants to write DAX?

Yes, assistants can draft measures quickly, but you still need to understand filter context to check whether the number is right.

Keep building your data skills

DAX is one piece of a bigger analyst toolkit: framing business questions, querying data with SQL, analyzing it with Python and turning results into recommendations. If you want structured, live practice with mentor feedback, take a look at these programs:

• Data Analytics Course

• AI Engineering Course