Chapter 4 · 9 levels

DAX basics: columns and measures

Calculated columns with IF, SWITCH, YEAR, MONTH and RELATED; measures with SUM, COUNTROWS, DISTINCTCOUNT, AVERAGE, MIN, MAX and DIVIDE; and the one question that decides between a column and a measure.

Start

With the model in place, the numbers come next. DAX (Data Analysis Expressions) is Power BI’s formula language, and it looks enough like Excel to feel familiar while working on whole columns and tables instead of cells.

DAX does two different jobs, and this chapter is about telling them apart. A calculated column adds a value to every row of a table when the data refreshes: a price band per product, a year per order, a cost per order line. A measure is computed on the fly for every cell of a visual, under whatever filters that cell has: customers per channel, average order value, price range by category.

You write both in practice visuals built for this site (Power BI Desktop runs only on Windows): a column becomes the rows of a matrix, a measure fills its cells, and a DAX engine checks each cell against the answer. Every lesson also gives the exact place to do it in Power BI Desktop. The chapter ends with a margin review for Harbor & Pine’s finance team that uses everything.

Levels

  1. A price band column with IF

    from Olivia Bennett, Merchandising Lead

    calculated column, if, row context, dax

    Code

  2. An order month column with MONTH

    from Chris Martinez, Marketing Planner

    calculated column, month, year, date functions, seasonality

    Code

  3. Quantity bands with SWITCH(TRUE())

    from Nicole Adams, Fulfillment Manager

    calculated column, switch, nested if, bands, data quality

    Code

  4. Count customers with DISTINCTCOUNT

    from Andrew Hughes, Customer Insights Analyst

    measure, distinctcount, countrows, count, filter context

    Code

  5. Price range with MIN and MAX

    from Jessica Ward, Pricing Manager

    measure, min, max, average, measure reference

    Code

  6. Column or measure?

    from Ashley Morgan, Reporting Analyst

    calculated column, measure, row context, filter context, dax

    Quiz

  7. Average order value with DIVIDE

    from Steven Clark, Head of E-commerce

    measure, divide, ratio, average order value, measure reference

    Code

  8. Line cost with RELATED

    from Laura Simmons, Category Finance Partner

    calculated column, related, relationship, row context, cost of goods sold

    Code

  9. Boss: the category margin review

    from Laura Simmons, Category Finance Partner

    calculated column, measure, related, isblank, divide, distinctcount

    Boss · 3 steps

Cookie settings

Pick what may run on this site. You can change this any time from "Cookie settings" at the bottom of every page.