DAX vs M, and DAX Syntax
Everything Arjun has written so far is M: it runs at refresh and shapes the data on its way in. Now Meera asks, "What share of revenue came from Pune — and from whichever dates I pick?" That answer depends on what she has selected on the page at the moment she looks. Questions like that are the job of Power BI's second language, DAX (Data Analysis Expressions).
The rule
M shapes data before it loads; DAX calculates answers after it loads.
| M (Power Query) | DAX | |
|---|---|---|
| Runs | At refresh | In the model: measures whenever a visual asks; calculated columns at refresh |
| Works on | One query at a time, step by step | Loaded tables and their relationships, under the page's filters |
| Typical jobs | Clean, type, split, merge, append | Totals, ratios, comparisons, time calculations |
| Looks like | let … in with steps |
Excel-style formulas |
| Case | Case-sensitive | Function names are not case-sensitive |
A useful split: if a value belongs to a row and never changes with what is selected, shape it in M. If it depends on slicers, filters and the visual it sits in, write it in DAX.
A DAX measure looks like this:
Total Revenue = SUM ( Sales[Line Revenue] )
Total Qty = SUM ( Sales[Qty] )
Avg Price Paid = DIVIDE ( [Total Revenue], [Total Qty] )Reading the parts:
Total Qty =names the measure. Names may contain spaces.SUM ( … )is a function; arguments go inside parentheses, separated by commas.Sales[Qty]is a column: the table name, then the column name in square brackets. If a table name contains spaces, wrap it in single quotes:'Store List'[City].[Total Revenue]on its own is a measure reference. Measures are written without a table name, so you can tell them from columns at a glance.- Operators:
+ - * /for arithmetic,&joins text,&&means and, and comparisons are= <> > >= < <=. DIVIDE ( a, b )is safe division: whenbis 0 or blank it returns BLANK, where plaina / bwould give infinity or a not-a-number result.//starts a comment.
Over all six January orders, Total Revenue is 600, Total Qty is 20, and Avg Price Paid is 600 / 20 = 30.
The shortcut
Read any DAX reference by its shape: Table[Column] is a column. [Name] alone is a measure. 'Quoted Name'[Column] is a column in a table whose name has spaces.
The name
Avg Price Paid =means a measure is being defined.
Drills
Answer fast — trust the rule.
Which task belongs in M rather than DAX?
In Avg Price Paid = DIVIDE ( [Total Revenue], [Total Qty] ), what is [Total Qty]?
What does DIVIDE ( 10, 0 ) return?
How do you reference the City column in a table named Store List?
Over the whole Sales table, what is Avg Price Paid?
Remember
- M shapes data before it loads; DAX calculates answers after it loads.
- Read references by shape:
Table[Column]is a column,[Name]alone is a measure. - Trap: dividing with
/where the denominator can be zero or blank — use DIVIDE.