PathTailor
FRSign inBuild my path
PathTailor
Build my pathSign inLire en français

Measures and Calculated Columns

First lesson of the DAX Fundamentals course, free to read: about 15 minutes, 4 exercises and 3 quiz questions. This is the catalogue's base lesson; in the student space, it is rewritten for your profile.

Objectives

  • Distinguish a measure from a calculated column
  • Write a properly named and formatted DAX measure
  • Organize measures in the model

MeasuresCalculated columnsDAX syntax

DAX, the Language of Calculations

DAX (Data Analysis Expressions) is the formula language of the Power BI model (and of Power Pivot in Excel, and of Analysis Services). Its formulas resemble Excel’s, but instead of working on cells, they work on tables and columns. You write DAX in three places: measures, calculated columns and calculated tables (like the date table).

The Calculated Column

A calculated column adds a column to a table. Its formula is evaluated row by row (this is called row context), at refresh time, and the result is stored in the model.

In Table view, select DimProduit, then New column:

Libellé = DimProduit[Categorie] & " - " & DimProduit[Produit]

Each row gets its value (“Boissons - Eau”). A calculated column is used to filter, group or sort: you can put it in a slicer or an axis.

The Measure

A measure is not stored: it is computed on demand, for each cell of a visual, according to the active filters (the filter context). Select the fact table then New measure:

Total ventes = SUM ( FaitVentes[Montant] )

In an unfiltered card, it is 415. In a column chart by store, it is 148 for Lyon, 135 for Lille, 132 for Nantes: the same formula gives a different result in each cell, because each cell has its own filter context.

When to Use Which?

Need Choice
An aggregated number that must react to filters (revenue, margin, rate) Measure
An attribute to filter, group, sort (band, label) Calculated column (or better, Power Query)
A ratio (margin %, achievement rate) Always a measure: a ratio can’t be summed

When in doubt, choose the measure: it uses no memory and stays correct at every level of aggregation. And remember that preparation columns are preferably created in Power Query.

Conventions and Best Practices

  • Reference a column with its table: FaitVentes[Montant].
  • Reference a measure without a table: [Total ventes]. This way you see at a glance what is a column and what is a measure.
  • Reuse measures: Total ventes TTC = [Total ventes] * 1.2.
  • Format the code: line breaks (Shift+Enter in the formula bar) and comments.
Total ventes TTC =
-- 20% VAT applied to revenue excluding tax
[Total ventes] * 1.2
  • Format the measure just once (Measure tools: currency, percentage, decimals).
  • Store measures in a measures table or in display folders.
  • In the model settings, the option that discourages implicit measures forces authors to use your explicit measures.

Note that DAX uses the period as the decimal separator and the comma to separate arguments, whatever the regional settings (default behavior of Power BI Desktop, changeable in the options).

Exercises

Solutions and tests stay on the server. In the student space, your code is run and checked, and the tutor gives hints before the solution.

  1. TaskDifficulty 12 hints

    Create your first measures

    Starting model (the one from the modeling course): FaitVentes (12 rows: NumVente, Date, DateLivraison, CodeMagasin, CodeProduit, Quantite, PrixUnitaire, Montant), DimMagasin (CodeMagasin, Magasin, Region), DimProduit (CodeProduit, Produit, Categorie) and Dates (2026), linked in a star. If you don’t have it, create C:\Formation\ventes_plat.csv with the content below, then build the three tables by reference in Power Query (Montant = Quantite × PrixUnitaire) and the many-to-one relationships.

    NumVente,Date,DateLivraison,CodeMagasin,Magasin,Region,CodeProduit,Produit,Categorie,Quantite,PrixUnitaire
V001,2026-01-03,2026-01-05,M1,Lyon,Auvergne-Rhône-Alpes,P01,Jus d'orange,Boissons,10,3
V002,2026-01-03,2026-01-06,M2,Lille,Hauts-de-France,P02,Pates,Epicerie,20,2
V003,2026-01-10,2026-01-12,M3,Nantes,Pays de la Loire,P03,Yaourt,Frais,30,1
V004,2026-01-17,2026-01-19,M1,Lyon,Auvergne-Rhône-Alpes,P04,Riz,Epicerie,15,2
V005,2026-01-24,2026-01-27,M2,Lille,Hauts-de-France,P05,Eau,Boissons,50,1
V006,2026-02-02,2026-02-04,M3,Nantes,Pays de la Loire,P01,Jus d'orange,Boissons,12,3
V007,2026-02-07,2026-02-09,M1,Lyon,Auvergne-Rhône-Alpes,P06,Fromage,Frais,8,6
V008,2026-02-14,2026-02-16,M2,Lille,Hauts-de-France,P03,Yaourt,Frais,25,1
V009,2026-02-21,2026-02-24,M3,Nantes,Pays de la Loire,P02,Pates,Epicerie,18,2
V010,2026-02-28,2026-03-03,M1,Lyon,Auvergne-Rhône-Alpes,P05,Eau,Boissons,40,1
V011,2026-03-06,2026-03-09,M2,Lille,Hauts-de-France,P04,Riz,Epicerie,10,2
V012,2026-03-13,2026-03-16,M3,Nantes,Pays de la Loire,P06,Fromage,Frais,5,6
    1. Create a measures table _Mesures (Home > Enter data, then delete the default column once a measure has been added).
    2. Create the measures Total ventes, Total quantités and Total ventes TTC (20% VAT, reusing [Total ventes]).
    3. Format Total ventes and Total ventes TTC as € currency, 0 decimals.
    4. Display the three measures in cards, then in a table by store.
    • Total ventes = €415
    • Total quantités = 243
    • Total ventes TTC = €498
    • By store: Lyon 148, Lille 135, Nantes 132 (incl. VAT: 177.60; 162; 158.40)

  2. TaskDifficulty 21 hint

    Calculated column or measure?

    Work on the star model used since lesson 1 (FaitVentes, DimMagasin, DimProduit, Dates; total sales 415).

    1. Add to DimProduit the calculated column Libellé = Categorie & ” - ” & Produit.
    2. Use it in a slicer.
    3. Add to FaitVentes a calculated column Prix x Qté = Quantite × PrixUnitaire and compare its sum to Total ventes.
    4. Delete this column (redundant with Montant) and explain in one sentence why a measure was preferable.
    • The slicer shows 6 labels, for example "Frais - Fromage"
    • The sum of the Prix x Qté column is 415
    • The redundant column is deleted

  3. CodeDifficulty 12 hints

    Write a measure

    Write the measure Prix moyen pondéré, which divides [Total ventes] by [Total quantités] (two existing measures).

    Prix moyen pondéré =
    -- à compléter

  4. Multiple choiceDifficulty 21 hint

    Where to write a rate?

    You need to compute a margin rate. What do you create?

    • A calculated column in the fact table
    • A measure
    • A column in Power Query
    • A calculated table

End-of-lesson quiz

  1. When is a calculated column evaluated?

    • At every click in the report
    • At model refresh, row by row
    • Never

  2. How do you reference a measure according to best practices?

    • FaitVentes[Total ventes]
    • [Total ventes]
    • 'Total ventes'

  3. Why does the Total ventes measure show 148 for Lyon?

    • Because it is stored per store
    • Because it is evaluated in the cell's filter context
    • Because Lyon comes first

The rest of the course

  1. Aggregations and Iterators (SUM, COUNTROWS, SUMX...)20 min
  2. Conditional Logic, DIVIDE and Blank Values15 min
  3. CALCULATE: Modifying the Filter Context20 min
  4. Variables, RELATED and Table Functions20 min
Build my pathSee the full syllabus

DAX Fundamentals · Intermediate · 1 h 30 min