Power BI Fundamentals Track
A six-course DataCamp track that walks complete beginners through Power BI basics, DAX formulas, data cleanup, and dashboard building.
DAX (Data Analysis Expressions) is the formula language used in Power BI, Excel Power Pivot and Analysis Services. It looks like Excel formulas, but it works on whole tables and columns instead of single cells. Most DAX is written as measures: calculations that recompute automatically for every filter, slicer and chart they appear in.
Most people who give up on Power BI give up at DAX. It isn't that the functions are hard. SUM and AVERAGE work the way you'd expect. The problem is that DAX evaluates formulas in a different way from Excel, and nobody explains that difference up front. This guide starts there.
The drag-and-drop side of Power BI takes you a long way: connect to data, drop a field on a chart, and Power BI sums it for you. You hit the limit the first time a stakeholder asks for something like:
None of these can be built by dragging fields. You need DAX. It's also a large part of the PL-300 exam: the "Model the data" area makes up 25–30% of the exam, and Microsoft's current skills list names CALCULATE, time intelligence, semi-additive measures and calculation groups directly. See our PL-300 exam guide for the full breakdown.
This is the first decision you make every time you write DAX, and beginners usually get it wrong.
| Calculated column | Measure | |
|---|---|---|
| Computed when | Once, when the data refreshes | Every time a visual is displayed or filtered |
| Stored in the model? | Yes, it takes memory like any other column | No, it's calculated on demand |
| Evaluated per | Row (row context) | Whatever the visual is showing (filter context) |
| Good for | Categorizing rows, like Price Band = IF(Sales[Price] > 100, "Premium", "Standard") |
Aggregations: totals, ratios, % of total, year-over-year |
| Can go on a slicer or axis? | Yes | No |
Rule of thumb: if you'd use the result to filter or group by, make it a column. If you'd use it as a number in a visual, make it a measure. In most well-built models, the large majority of DAX is measures.
Assume a simple model: a Sales fact table with Amount, Cost, Quantity and Unit Price, connected to Date, Product and Region tables.
Total Sales = SUM ( Sales[Amount] )
Total Cost = SUM ( Sales[Cost] )
Total Profit = [Total Sales] - [Total Cost]
Margin % = DIVIDE ( [Total Profit], [Total Sales] )
Two habits worth building from day one:
[Total Sales]) instead of repeating SUM(...). When the definition of "sales" changes, you fix it in one place.DIVIDE instead of /. DIVIDE returns a blank instead of an error when the denominator is zero, so a region with no sales won't break your visual.Put Margin % into a matrix with regions on the rows. The same measure returns a different number in every cell, and you never told it which region to use.
That's filter context: the set of filters active for a particular cell. For the "North" row, the filter context is Region = North, plus any slicer selections, page filters and report filters. The measure runs once per cell, inside that cell's filters.
This is the core difference from Excel. In Excel, a formula points at specific cells. In DAX, a measure describes how to calculate, and the visual decides what data it calculates over. Once that clicks, most of DAX follows from it.
Row context is the other kind of context. It exists when DAX is working through a table one row at a time, which happens in calculated columns and in "X" functions such as SUMX:
Revenue = SUMX ( Sales, Sales[Quantity] * Sales[Unit Price] )
SUMX multiplies quantity by price for each row and then adds up the results. That's why you use it when the calculation has to happen before the aggregation. A plain SUM can't do that.
CALCULATE is the most important function in DAX. It evaluates an expression inside a modified filter context: it can add filters, replace filters or remove them.
Adding a filter:
West Sales = CALCULATE ( [Total Sales], Region[Region] = "West" )
This returns West's sales in every cell, whichever region the row shows.
Removing a filter to get "% of total":
Sales % of All Regions =
DIVIDE (
[Total Sales],
CALCULATE ( [Total Sales], ALL ( Region ) )
)
Worked example. Say North sold $400K, South $250K and West $350K, for a total of $1,000K. In the West row:
[Total Sales] is evaluated with the filter Region = West, so it returns $350KCALCULATE([Total Sales], ALL(Region)) removes the region filter, so it returns $1,000KThe measure keeps working when someone filters the report to one product category, because ALL(Region) removes only the region filter and leaves the product filter in place. That selectivity is what makes CALCULATE so useful.
Time comparisons are the most common requests in business reporting, and DAX has built-in functions for them:
Sales YTD = TOTALYTD ( [Total Sales], 'Date'[Date] )
Sales LY = CALCULATE ( [Total Sales], SAMEPERIODLASTYEAR ( 'Date'[Date] ) )
Sales YoY % = DIVIDE ( [Total Sales] - [Sales LY], [Sales LY] )
If this year's sales in a period are $1.2M and the same period last year was $1.0M, Sales YoY % returns 20%.
The prerequisite everyone skips: time intelligence needs a proper date table. That means one row per day, no gaps, covering the full range of your data, and marked as a date table in Power BI. Relying on the automatic date hierarchy works for simple charts but causes confusing results once you write time intelligence measures. "Create a common date table" is its own line item in the PL-300 skills list for this reason.
Once measures get longer, use VAR and RETURN:
Sales YoY % =
VAR CurrentSales = [Total Sales]
VAR LastYearSales = [Sales LY]
RETURN
DIVIDE ( CurrentSales - LastYearSales, LastYearSales )
Variables are computed once and reused, so they're often faster as well as easier to debug. You can temporarily RETURN any variable to check its value.
| Excel formulas | DAX | |
|---|---|---|
| Works on | Cells and ranges (A2:A100) |
Tables and columns (Sales[Amount]) |
| Result depends on | The cells referenced | The filter context of the visual |
| Lookups | VLOOKUP / XLOOKUP |
Relationships in the model, plus RELATED |
| Conditional sums | SUMIFS |
CALCULATE with filters |
| Typical mistake coming from Excel | — | Trying to point at "the cell above" instead of thinking in filters |
If you're coming from Excel, the Excel vs Power BI comparison covers what else changes when you move over.
DIVIDE([Total Profit], [Total Sales])) so it's correct at every level.FILTER inside CALCULATE when a simple condition would work. CALCULATE([Total Sales], Region[Region] = "West") is clearer and usually faster than CALCULATE([Total Sales], FILTER(Sales, ...)) over the full fact table.Partly. Copilot in Power BI and general AI assistants can draft measures from a plain-English description, and they do well on standard patterns like YTD and % of total. The catch is that the output can be syntactically valid and still wrong for your model, for example by removing the wrong filter or ignoring your date table. You need to understand filter context to catch those errors. That's the same pattern we describe in Will AI Replace Data Analysts?: AI speeds up people who can check its work.
Is DAX hard to learn? The basics (SUM, DIVIDE, simple CALCULATE) take a few days for anyone comfortable with Excel. What takes weeks is getting used to filter context. Expect a period where measures return numbers you didn't expect. Working out why is how you learn it.
Do I need to learn DAX to use Power BI? Not for simple reports. For anything a stakeholder will rely on (margins, comparisons, % of total), yes. And you can't pass PL-300 without it.
Is DAX the same as Power Query (M)? No. Power Query (the M language) shapes and cleans data before it loads into the model. DAX calculates on top of the loaded model. A good rule is to do cleaning and reshaping in Power Query and business calculations in DAX.
Where else is DAX used besides Power BI? Excel's Power Pivot, SQL Server Analysis Services and Azure Analysis Services, and Microsoft Fabric semantic models all use DAX. What you learn carries over across the Microsoft data stack.
DAX is Power BI's calculation language. It's harder than Excel formulas for one reason: results depend on filter context, not on fixed cell references. Learn to separate measures from calculated columns, understand how CALCULATE changes the filter context, and build a proper date table. Those three things cover most of the DAX that real reports and the PL-300 exam need.
A six-course DataCamp track that walks complete beginners through Power BI basics, DAX formulas, data cleanup, and dashboard building.
Launch your career in data analytics with this comprehensive Power BI course designed for beginners and aspiring analysts.