DAX in one lesson: from your first measure to the pitfalls

In the article on SQL the topic was the language your database answers in. DAX is the language Power BI calculates in. The difference is bigger than it looks.

An SQL query returns one table. A DAX formula returns a number, and recalculates it for every cell in your report, for every filter someone clicks. Understand that difference and you understand half of DAX. The other half is the pitfalls, and those are covered below as well.

The examples come from a model I built: invoices in a table fct_factuurlijn, surrounded by a calendar, channels, branches and products. How that is put together is in the article on the data model.

1. Column or measure

In Power BI you can calculate in two places. A calculated column calculates once per row, at refresh, and stores the result. A measure only calculates when someone looks, and always for the selection that applies at that moment.

The rule of thumb: anything you add up, divide or compare is a measure. You use a column only for something you want to filter or group on, such as an age bracket or a price range.

The classic mistake is a margin percentage as a column. Then every invoice line has a percentage, and whoever puts that in a visual gets the sum or the average of percentages. Both are wrong. More on that in step 4.

2. Your first measure, and what filter context is

Revenue = SUM ( fct_factuurlijn[omzet] )

That is all. Put that measure in a table with channels in the rows, and you get revenue per channel. Put it in a card with a filter on one year, and you get the revenue for that year. The same formula, a different answer every time.

That is because of the filter context: the set of filters that applies to that one cell. The row in your table, the slicer at the top, the filter on the page. For every cell DAX looks at which filters apply, and only adds up those rows.

That is the most important idea in DAX. A measure does not know which visual it sits in. It only sees filters.

3. CALCULATE: changing the filter itself

CALCULATE is the function everything revolves around. It evaluates a measure with adjusted filters.

Revenue webshop =
CALCULATE ( [Revenue], dim_kanaal[naam] = "Webshop" )

And to remove all filters on channel:

Revenue all channels =
CALCULATE ( [Revenue], REMOVEFILTERS ( dim_kanaal ) )

Channel share =
DIVIDE ( [Revenue], [Revenue all channels] )

Put Channel share in a table per channel, and every row shows its part of the total. The numerator follows the row, the denominator ignores it.

Pitfall: put Revenue webshop in that same table per channel, and the Retail row also shows the webshop revenue. CALCULATE replaces the filter on the channel, it does not add one. If you want the row to count, write:

Revenue webshop =
CALCULATE ( [Revenue], KEEPFILTERS ( dim_kanaal[naam] = "Webshop" ) )

Now the Retail row is empty and the Webshop row is filled, which is probably what you meant.

4. Iterators: calculating row by row

SUM adds up a column. SUMX goes through a table row by row, evaluates an expression per row, and then adds up.

Cost of goods sold =
SUMX (
    fct_factuurlijn,
    fct_factuurlijn[aantal] * RELATED ( dim_product[kostprijs] )
)

RELATED fetches the cost price from the product table for every invoice line. That only works inside an iterator, because there has to be a row to start from.

Then the margin percentage, done right:

Margin = SUM ( fct_factuurlijn[marge] )
Margin % = DIVIDE ( [Margin], [Revenue] )

Pitfall: the average of percentages. An invoice of 10 euros at 50 percent margin and one of 10,000 euros at 10 percent do not add up to 30 percent together. They give just over 10. Always divide the sum by the sum, never average the ratios.

5. Time: last year, year to date

Revenue last year =
CALCULATE ( [Revenue], SAMEPERIODLASTYEAR ( dim_datum[datum] ) )

Revenue YTD =
TOTALYTD ( [Revenue], dim_datum[datum] )

Those functions only work with a proper calendar table. It has to contain every day, without gaps, and be marked as a date table. And the relationship with your invoices has to be on the date, not on a text field.

Pitfall one: a calendar that runs further than your data. Measures anchored on the last date then come back empty. That is worked out in the article on empty measures.

Pitfall two: this year against last year at day level, while your invoices cluster on fixed days. Then you are comparing arbitrary moments. See why a day-level comparison lies to you.

6. VAR: readable and faster

With VAR you store an intermediate result under a name. That makes a formula readable, and DAX evaluates each variable only once.

Growth % =
VAR current = [Revenue]
VAR previous = [Revenue last year]
RETURN
    DIVIDE ( current - previous, previous )

DIVIDE instead of a slash is not a detail. If previous is zero or empty, a slash gives an error or infinity. DIVIDE gives an empty value, and an empty cell is always better in a report than an error message.

7. The pitfalls that make your report quietly lie

Some mistakes do not produce an error. They just produce a wrong number.

Relationships in both directions. They solve one problem and create two: filters travel along routes you do not expect, and the model gets slower. Let filters flow from the dimension to the fact table, one direction. Both directions only where there is really no other way, and preferably on a one-to-one relationship.

Zero instead of empty. Whoever writes [Revenue] + 0 to fill empty cells gets a table with every product in every month, including where nothing was ever sold. Leave empty as empty.

FILTER over an entire table. CALCULATE ( [Revenue], FILTER ( fct_factuurlijn, ... ) ) makes DAX walk row by row through your largest table. Where you can, filter on a column, as in step 3. The result is the same, the speed is not.

Implicit measures. Dragging a column straight into a visual quietly creates a sum or an average. That works, until someone builds another visual and gets another aggregation. Write every calculation as an explicit measure, then it exists only once.

How I organise measures

All my measures sit in a separate table, without data, with folders: Result, Volume, Control and Time.

I build from the bottom up. First the basics, such as Revenue, Margin and Quantity. Then everything that builds on them: percentages, last year, growth. That way the definition of revenue sits in one place, and changes in one place. That is settling your definitions, but in code.

The Control folder holds measures that are supposed to be zero: lines without a branch, invoices without a date. And I regularly put revenue in the model next to the same sum in SQL on the database. If they match, the layer underneath is right.

The claim

DAX is not hard because the functions are hard. It is hard because a mistake does not give an error message but a number. Understand filter context and write every measure once, and you have already avoided most of those mistakes.