How DAX thinks

01The problem

Kolanut Distribution's sales manager opens last month's Power BI report and sees three different revenue figures on three pages: ₦830.5m, ₦859.6m and ₦831.0m. One visual summed the list price, one summed revenue before discounts, and one summed a column somebody had rounded. Each was "Sum of something", dragged straight into a visual.

Nobody trusts the report now, and that's the real cost. A model with one Revenue measure, defined once and reused everywhere, can't disagree with itself.

In Power BI Fundamentals you wrote your first measures. This course goes deep: how DAX really evaluates a formula, CALCULATE, time intelligence, table functions, ranking, customer analytics and performance. It starts with a model and measures you can trust.

02The concept

The model comes first

DAX is evaluated over the model: tables joined by relationships, with filters flowing from the "one" side to the "many" side. Kolanut's model is a small star schema:

Filters flow downhill, from a dimension to the fact. Selecting "Lagos" in customers[region] filters orders. Selecting a product filters orders too. But nothing flows back up: filtering orders doesn't filter products. You'll meet the consequences of that in almost every lesson.

Measures, not dragged columns

Every number in a report should come from an explicit measure: written once, named clearly, formatted, and reused. Dragging a column into a visual creates an "implicit measure" that nobody can find, check or reuse.

Base measures first, then build on them

Write a handful of simple base measures, then build everything else from them. When the definition of revenue changes, you change one measure and the whole report follows.

Habits of a trustworthy measure library

• Keep measures in a dedicated _Measures table, in display folders (Sales, Customers, Time).
• Name them as a manager would say them: Revenue, Discount %, Active Customers.
• Format every measure (₦ with thousands separators, % with one decimal place).
• Write a one-line description on each measure (Properties pane). It shows as a tooltip in the Data pane.

03Example

Kolanut's base measures. Revenue is calculated from the order lines, so it doesn't depend on a column someone added in Power Query:

Revenue =
SUMX (
    orders,
    orders[quantity] * orders[unit_price] * ( 1 - orders[discount_pct] / 100 )
)

Gross Revenue =
SUMX ( orders, orders[quantity] * orders[unit_price] )

Discount Amount = [Gross Revenue] - [Revenue]

Discount % = DIVIDE ( [Discount Amount], [Gross Revenue] )

Order Lines = COUNTROWS ( orders )

Units = SUM ( orders[quantity] )

Active Customers = DISTINCTCOUNT ( orders[customer_id] )

The three figures from the problem are now three named measures: Revenue (₦830.5m, what customers actually pay), Gross Revenue (₦859.6m, before discounts) and the difference between them, Discount Amount. Each one is clearly labelled, and none of them can be confused with another.

04Walkthrough

1. Download the sales dataset and load orders.csv, customers.csv and products.csv into Power BI Desktop. In Power Query, check that order_date is a Date and that quantity, unit_price and discount_pct are Whole number.
2. Create the date table with Modeling → New table:

Date =
ADDCOLUMNS (
    CALENDAR ( DATE ( 2025, 1, 1 ), DATE ( 2026, 12, 31 ) ),
    "Year", YEAR ( [Date] ),
    "Quarter", "Q" & ROUNDUP ( MONTH ( [Date] ) / 3, 0 ),
    "Month Number", MONTH ( [Date] ),
    "Month", FORMAT ( [Date], "mmm" ),
    "Year Month", FORMAT ( [Date], "yyyy-mm" )
)

3. Mark it as a date table (Table tools → Mark as date table, column Date), and sort Month by Month Number.
4. In Model view, create the three relationships in the table above. Check each is one-to-many with a single cross-filter direction.
5. Create a _Measures table (Home → Enter data, load an empty table), then add the seven base measures from the example.
6. Format them: Revenue, Gross Revenue and Discount Amount as currency with no decimals; Discount % as a percentage with one decimal place; the counts as whole numbers.
7. Put the measures into a display folder called Sales (Model view → select the measures → Properties → Display folder), and give Revenue the description "What customers pay: quantity × price, after discount."
8. Check your model: a card with Revenue should show ₦830,541,245.

05Practice

06More practice

Optional drills. They don't count towards the certificate.

07Check your understanding