Power BI DAX basics: measures, context and a few key functions
Power BI DAX is the formula language behind every calculated number in a report. DAX stands for Data Analysis Expressions, and it looks a little like Excel formulas at first glance. However, it works on whole tables and on the filters a reader applies, not on single cells. This guide explains the core ideas in plain words, so a business reader can follow what an author builds.
- Plain-word explanations
- Six starter functions
- Common traps named
Last updated
Power BI DAX: what is it and why does it matter?
DAX is the language authors use to write measures, calculated columns and calculated tables. A measure is a named formula, such as total sales or margin rate. Once it exists, any visual can use it.
It matters because raw data seldom answers a business question directly. A sales table holds rows of orders. Leaders, however, ask about growth against last year, share of a category or average order value. DAX turns the rows into those answers.
It also keeps answers consistent. When every report reads the same measure, two pages cannot disagree about revenue. So a modest set of well-named measures is often the most valuable part of a model.
Three ideas every DAX beginner needs
Most confusion clears up once these three ideas make sense, so take them slowly.
Measures
A measure calculates when a visual asks for it. Because it follows the filters in play, one formula gives the right figure at every level.
Row context
A calculated column works through a table one row at a time. Inside it, a field name means the value on the current row.
Filter context
Slicers, axes and page filters narrow the rows a measure can see. The same measure then returns different results in each cell of a table.
Which DAX functions should a beginner learn first?
These six cover a large share of everyday measures, and Microsoft documents each one in its DAX reference.
| Function | What it does | Typical use |
|---|---|---|
| SUM | Adds the values in one column | Total sales or total cost |
| SUMX | Evaluates an expression per row, then adds the results | Quantity times unit cost |
| CALCULATE | Evaluates an expression under changed filters | Sales for one region or period |
| DIVIDE | Divides and handles a zero divisor safely | Margin rate or conversion rate |
| RELATED | Fetches a value from a related table | Product category on a sales row |
| DISTINCTCOUNT | Counts the different values in a column | Number of active customers |
How does CALCULATE change the way Power BI DAX works?
CALCULATE is the function that changes filters. It takes an expression, such as total sales, and a set of conditions. Then it evaluates the expression as if those conditions applied.
That ability unlocks comparisons. Sales for the prior year, sales for all regions and sales for one product line are all the same base measure under different filters. So an author writes the base once and wraps it as necessary.
Time calculations rely on it too. Year-to-date and same-period-last-year measures shift the date filter, which is why a proper date table matters. Because CALCULATE can also turn a row context into a filter context, beginners find it puzzling. Still, it rewards study more than any other function.
Power BI DAX habits that prevent wrong totals
Adopt these four habits early, since they are much harder to add to a finished model.
Start from a star schema
First shape the data into fact and dimension tables, because simple models need far simpler formulas.
Prefer measures to columns
Then write a measure whenever the result is a total or a ratio, so it stays correct under every filter.
Use variables
Next store intermediate results in variables. They make a formula easier to read and avoid repeated work.
Test against a known figure
Finally, compare each new measure with a number you trust, such as a total from the accounting system.
Power BI DAX questions from Excel users
Is DAX like Excel formulas?
Partly. Many function names match, and the syntax looks familiar. However, Excel formulas point at cells, while DAX points at columns and tables. There is no cell A1 to refer to. Therefore the habits that matter most are about filters and relationships, which Excel users rarely meet in a worksheet.
Do I need DAX for every Power BI report?
No. Simple reports can rely on the automatic sums that appear when you drag a number onto a visual. Still, explicit measures bring control over names, formats and logic. So most professional models define a measure for every figure, even the easy ones.
Why does my total not match the sum of the rows?
Because the total is not a sum of the visible rows. A measure recalculates for the total line under its own filters. With ratios or distinct counts, that result differs from adding the rows up. Usually the measure is right, and the surprise comes from expecting spreadsheet behavior.
What are variables for?
Variables hold a value or a table under a name inside one formula. They let you calculate something once and use it several times. As a result, the formula reads in clear steps and can run faster. Microsoft's guidance recommends them for both readability and performance.
Can Power BI Agency write or review our measures?
Yes. One of our consultants can review your existing measures or write new ones, working online inside your own tenant. We describe each formula in plain words, so your team can maintain it. We are a division of Sataware Technologies, independent of Microsoft. The first 30-minute assessment is free.
Let's build your first dashboard together.
Tell us what you report on today. A consultant replies within 2 hours with next steps.
Trusted partners
Microsoft Solutions Partner · AWS Partner
Cloud & AI Platforms
Azure SQL, Microsoft Fabric and the data pipelines behind your reports.
AI Business Solutions
Power BI reporting on your Dynamics 365, Business Central and Power Platform data.
Security
Row-level security and Entra ID sign-in on every report and workspace.
Amazon Web Services
Power BI on your AWS data, with connectors for Redshift, Athena and S3.