Webbo3 Power BI · Cohort 1 · Day 8 of 16

Day 8: Calculated Columns vs Measures and DAX Basics

The formula language of Power BI, and the one decision that confuses everybody: column or measure?

Power Query shaped the data. DAX is how you ask it questions: how much, how many, how fast, compared with what.

Today every formula is typed in the report, not in Power Query. Open your Day 7 file with the star schema and the Date table in place.

1. What DAX Is

Definition

DAX (Data Analysis Expressions) is the formula language of the Power BI model. It looks like Excel formulas, but it works on whole tables and columns, not on cells. You type it in the formula bar after choosing New measure or New column.

Real-Life Example

Excel is a calculator you point at one cell at a time. DAX is asking the accountant a question: "total revenue", and she knows to add up only the rows for the station, month or product you are currently looking at.

Types: The Three Things You Can Write in DAX

Reading a Formula

Every DAX formula starts with a name, then =, then the expression. Columns are written TableName[ColumnName], and a table name with spaces goes in single quotes: 'Sales Clean'[TotalAmount_NGN]. Measures are written in square brackets alone: [Total Revenue]. That difference tells you at a glance which is which.

2. Row Context and Filter Context

Definition

Row context means "this row": a formula that can see one row at a time and use its values. Calculated columns have it. Filter context means "these rows": the set of rows left after every filter currently applied (the visual's rows and columns, slicers, the filter pane). Measures are calculated in it.

Real-Life Example

A pump attendant filling one car is in row context: this car, these litres, this price. The station manager reading the day's total is in filter context: all the cars at this station, today. Same data, a different question.

3. Calculated Columns

Definition

A calculated column adds a column to a table, worked out row by row with row context, and saves every value in the model. Use one when you need the value to slice or group by, not to add up.

Real-Life Example

Writing on each receipt whether the quantity is in litres or in units, so nobody adds five litres of diesel to five tins of grease. The unit is a label on the row, not a total.

RELATED(Table[Column]) follows a relationship from the many side to the one side and fetches the value for this row. It only works in row context and only along a relationship.

1
2

// Fetch the unit of measure of the product sold on this row
Unit = RELATED ( Product_Master[UnitOfMeasure] )

Prefer Power Query for Columns

The same Unit could have been merged in Power Query on Day 5, just as City, Region and Category were. The rule most professionals follow: if a column can be made in Power Query, make it there (it is compressed better and kept with the rest of the cleaning). Use DAX columns when you need the model, such as RELATED.

The Classic Mistake: A Total in a Column

1
2

// WRONG: a calculated column that tries to hold an average
Avg Sale Column = AVERAGE ( Sales[TotalAmount_NGN] )

Every row holds the same number, ₦23,597.19, the average of the whole table, frozen at refresh. It will never change when the reader filters to Lekki or to 2024. Averages and totals belong in measures.

4. Measures

Definition

A measure is a named calculation that is worked out when a visual needs it, in the filter context of that visual. It is not stored per row. The same measure gives the grand total on a card, and a separate answer for each row of a table.

Real-Life Example

"Total offering" is a measure. Ask it for the whole church and it gives the whole church. Ask it for the youth service and it gives the youth service. You do not write a new formula for each; the question sets the context.

Types: The Aggregation Functions

A table called _Measures (with an underscore so it sorts to the top) is a common way to keep every measure in one place instead of scattered across tables.

1
2

// The sum of TotalAmount_NGN over whatever rows are in the current filter
Total Revenue = SUM ( Sales[TotalAmount_NGN] )

1
2

// How many sales rows are in the current filter
Transactions = COUNTROWS ( Sales )

1
2

// How many different attendant names appear in the current filter
Attendants Selling = DISTINCTCOUNT ( Sales[AttendantName] )

18 is 17 people plus "Unmanned", which DISTINCTCOUNT counts as one more name. Knowing exactly what a measure counts is half of writing it: on Day 10 you will learn to leave Unmanned out.

1
2

// Revenue per sale; DIVIDE returns blank instead of an error if there are no sales
Average Sale = DIVIDE ( [Total Revenue], [Transactions] )

Notice that Average Sale is built from other measures, written in square brackets. Measures reuse measures: define Total Revenue once and every later measure builds on it. DIVIDE is safer than /: filter to a station with no sales and / gives an error, while DIVIDE gives a blank.

Filter Context at Work

The total row is not the sum of the averages above it. It is the measure calculated again with no payment filter. Measures always recalculate for the total; they never just add up the rows.

5. Iterators: SUMX

Definition

An iterator is a function that goes through a table row by row (creating row context), works out an expression for each row, then aggregates the results. SUMX(table, expression) sums them; AVERAGEX, MAXX and COUNTX do the same with other aggregations.

Real-Life Example

A cashier at closing time who goes through every receipt, multiplies quantity by price on each, and adds the answers. No total column needed: the multiplication happens as she goes.

1
2
3
4
5
6

// For each sale: quantity times price. Then add all of those up.
Revenue (SUMX) =
SUMX (
    Sales,
    Sales[Quantity] * Sales[UnitPrice_NGN]
)

6. Naming and Formatting Measures

• Names a manager would say: Total Revenue, not SumRev1.
• Format as you create: select the measure, Measure tools, Format. For naira, choose Currency, or type a custom format ₦#,0.
• Build on measures: Average Sale uses [Total Revenue]; if the definition of revenue changes, you change one measure.
• Comment long formulas with //, as the examples above do.

Do This Now

1. Create a _Measures table.
2. Write Total Revenue, Transactions, Attendants Selling and Average Sale, and format each.
3. Add a Unit calculated column with RELATED.
4. Build the PaymentMethod table visual and read the total row.
5. Write Revenue (SUMX) and check it equals Total Revenue.
6. Write the wrong Avg Sale Column, see the same number on every row, then delete it.

Checkpoint

Next, Day 9 Lab: relationships in Model view and your first page of measures, built and checked together. Assignment 2 is released.