1.3.2. DAX: Calculating Over the Model
💡 First Principle: A business metric like "Gross Margin %" isn't a fixed number; it's a recipe that must produce the right answer for whatever slice a user is looking at: one product, one month, or the whole company. DAX expresses that recipe once so the engine can evaluate it for every slice on demand.
Data Analysis Expressions (DAX) is the formula language of the semantic model. It produces three kinds of objects, and choosing among them is a recurring exam decision:
- Measures are evaluated at query time in the current filter context and aren't stored. They're the right home for aggregations and ratios:
Total Sales = SUM(Sales[SalesAmount]). A measure responds to slicers, filters, and the row and column headers of a visual. - Calculated columns are evaluated once per row at refresh and stored in the table. They're right for values you need to slice, group, or sort by, or to use as a relationship key, such as an Age Band column on Customer.
- Calculated tables are evaluated at refresh and stored as whole tables, for example a date table built with
CALENDARor a role-playing copy of a dimension.
Implicit measures are created when you drag a numeric column into a visual and Power BI applies a default summarization (Sum, Average). Explicit measures are written by you. Best practice, and a frequent exam answer, is to write explicit measures: they can be reused, formatted once, documented, used in calculation groups, and referenced by other measures. Creating a calculation group actually discourages implicit measures model-wide for this reason.
DIVIDE(numerator, denominator) is preferred over the / operator because it returns BLANK (or an alternate result you specify) instead of an error when the denominator is zero, which keeps visuals clean.
⚠️ Exam Trap: A ratio such as margin percentage must be a measure, never a calculated column that you then sum or average. Summing per-row percentages gives meaningless totals; a measure computes DIVIDE(SUM(margin), SUM(sales)) for whatever slice is in view, so totals are correct.
Reflection Question: A report shows Average Unit Price as an implicit average of a Price column, and the total row looks wrong to the finance team. Explain what the implicit measure is actually averaging, and what explicit measure would answer the question they intend.
