DAX in Power BI: The Essential Formulas, With Examples

What is DAX in Power BI?

DAX (Data Analysis Expressions) is the formula language used in Power BI, Excel Power Pivot and Analysis Services to create measures, calculated columns and calculated tables. It looks like Excel formulas, but it works on tables and columns instead of cells, and its results change with the filters applied in the report. You do not need hundreds of functions: around fifteen cover most business dashboards.

All examples below use a simple star schema:

Table	Key columns
Sales (fact)	OrderID, CustomerID, ProductID, StoreID, Date, Quantity, UnitPrice, UnitCost, Amount
Product	ProductID, ProductName, Category
Store	StoreID, City, Region
Customer	CustomerID, CustomerName
'Date'	Date, Year, Month, MonthNumber (marked as date table)

If your model is not shaped like this yet, start with the Power BI star schema guide: DAX is much simpler on a clean model.

Measure or calculated column: which one should you use?

	Measure	Calculated column
Computed	At query time, for each visual cell	At refresh, once per row
Reacts to filters	Yes	No (stored value)
Memory	None until used	Increases model size
Typical use	Totals, ratios, KPIs	Categories to filter or group by

Rule of thumb: if you want to display a number, write a measure. If you want to slice by a value, a calculated column (or better, a Power Query column) is appropriate.

-- Calculated column in the Sales table
Line Margin = Sales[Quantity] * ( Sales[UnitPrice] - Sales[UnitCost] )

What are the basic DAX formulas?

Total Sales = SUM ( Sales[Amount] )

Orders = DISTINCTCOUNT ( Sales[OrderID] )

Active Customers = DISTINCTCOUNT ( Sales[CustomerID] )

Sales Lines = COUNTROWS ( Sales )

Average Basket = DIVIDE ( [Total Sales], [Orders] )

Always prefer DIVIDE to the / operator: it returns BLANK instead of an error when the denominator is zero, and an optional third argument lets you choose another value.

How do iterators like SUMX work?

Iterators (SUMX, AVERAGEX, MINX, MAXX, COUNTX) evaluate an expression row by row, then aggregate. Use them when the calculation must happen before the sum:

Total Margin =
SUMX ( Sales, Sales[Quantity] * ( Sales[UnitPrice] - Sales[UnitCost] ) )

Margin % = DIVIDE ( [Total Margin], [Total Sales] )

This replaces the calculated column above and saves memory.

How does CALCULATE work?

CALCULATE is the most important DAX function: it evaluates an expression in a modified filter context. Every "sales for X", "share of total" or "same period last year" measure uses it.

Add or replace a filter

Sales Casablanca =
CALCULATE ( [Total Sales], Store[City] = "Casablanca" )

Sales Casablanca or Rabat =
CALCULATE ( [Total Sales], Store[City] IN { "Casablanca", "Rabat" } )

Remove filters to compute a share of total

Share of All Products =
DIVIDE (
    [Total Sales],
    CALCULATE ( [Total Sales], REMOVEFILTERS ( Product ) )
)

Share of Selection =
DIVIDE (
    [Total Sales],
    CALCULATE ( [Total Sales], ALLSELECTED ( Product ) )
)

The first ignores every product filter; the second respects what the user selected in slicers but ignores the row of the visual.

Keep the existing filter

By default, a filter in CALCULATE overwrites an existing filter on the same column. KEEPFILTERS intersects instead:

Sales Appliances (kept) =
CALCULATE ( [Total Sales], KEEPFILTERS ( Product[Category] = "Appliances" ) )

Filter on a measure with FILTER

A simple Boolean filter in CALCULATE cannot reference a measure. Use FILTER over a table of values instead:

Customers Above 100k =
COUNTROWS (
    FILTER ( VALUES ( Sales[CustomerID] ), [Total Sales] > 100000 )
)

What are the essential time intelligence formulas?

Time intelligence functions need a date table with one row per day, no gaps, marked as date table and related to the fact table.

Sales YTD = TOTALYTD ( [Total Sales], 'Date'[Date] )

Sales LY =
CALCULATE ( [Total Sales], SAMEPERIODLASTYEAR ( 'Date'[Date] ) )

Sales Previous Month =
CALCULATE ( [Total Sales], DATEADD ( 'Date'[Date], -1, MONTH ) )

Sales Rolling 12M =
CALCULATE (
    [Total Sales],
    DATESINPERIOD ( 'Date'[Date], MAX ( 'Date'[Date] ), -12, MONTH )
)

Sales 3M Monthly Average =
DIVIDE (
    CALCULATE (
        [Total Sales],
        DATESINPERIOD ( 'Date'[Date], MAX ( 'Date'[Date] ), -3, MONTH )
    ),
    3
)

For a fiscal year that does not end on 31 December, pass the year-end date: TOTALYTD ( [Total Sales], 'Date'[Date], "06-30" ).

How do you write cleaner DAX with variables?

VAR stores an intermediate result, evaluated once, and RETURN gives the final value. Variables make measures easier to read and often faster:

Sales YoY % =
VAR CurrentSales = [Total Sales]
VAR PreviousSales =
    CALCULATE ( [Total Sales], SAMEPERIODLASTYEAR ( 'Date'[Date] ) )
RETURN
    IF (
        NOT ISBLANK ( PreviousSales ),
        DIVIDE ( CurrentSales - PreviousSales, PreviousSales )
    )

Ranking, top N and dynamic text

Product Rank =
RANKX ( ALL ( Product[ProductName] ), [Total Sales], , DESC, DENSE )

Sales Top 5 Products =
CALCULATE (
    [Total Sales],
    TOPN ( 5, ALL ( Product[ProductName] ), [Total Sales], DESC )
)

Report Title =
"Sales - " & SELECTEDVALUE ( Store[City], "All cities" )

Report Title can be used as a dynamic title (Title > fx) so the header changes with the slicer.

Segmenting with SWITCH

A calculated column in the Customer table that classifies customers by total revenue:

Customer Segment =
VAR CustomerSales = CALCULATE ( SUM ( Sales[Amount] ) )
RETURN
    SWITCH (
        TRUE (),
        CustomerSales >= 100000, "Key account",
        CustomerSales >= 20000, "Mid-size",
        "Small"
    )

The CALCULATE without filter triggers context transition: the current customer row becomes a filter on the Sales table.

Cheat sheet: which DAX function for which need?

Need	Function(s)
Sum, count, distinct count	SUM, COUNTROWS, DISTINCTCOUNT
Safe ratio	DIVIDE
Row-by-row calculation	SUMX, AVERAGEX
Change filters	CALCULATE, REMOVEFILTERS, ALL, ALLSELECTED, KEEPFILTERS
Complex condition	FILTER
Year-to-date, last year, rolling	TOTALYTD, SAMEPERIODLASTYEAR, DATEADD, DATESINPERIOD
Stock at end of period	CALCULATE with LASTDATE
Ranking	RANKX, TOPN
Selected value, dynamic label	SELECTEDVALUE
Multiple conditions	SWITCH ( TRUE (), … )

Testing a measure in DAX query view

Power BI Desktop includes a DAX query view where you can check a measure as a table:

EVALUATE
SUMMARIZECOLUMNS (
    'Date'[Year],
    Store[City],
    "Sales", [Total Sales],
    "YoY %", [Sales YoY %]
)
ORDER BY 'Date'[Year], Store[City]

Compare the result with the source system before putting the measure on a page.

What are the most common DAX mistakes?

• Using calculated columns for totals and ratios that should be measures.
• Dividing with / and getting errors or infinity on empty periods.
• Time intelligence without a proper, continuous date table.
• Bi-directional relationships used to "fix" a measure, creating ambiguous results.
• Ratios computed as an average of row-level ratios instead of a ratio of totals.

The official function reference on Microsoft Learn (DAX reference) is the place to check every argument.

How should you organise and name your measures?

• Keep all measures in one dedicated table (for example _Measures) and group them in display folders: Sales, Margin, Time, Customers.
• Use explicit, business-readable names: Sales YoY % is better than Measure 12.
• Build complex measures on top of simple ones ([Total Sales], then [Sales LY], then [Sales YoY %]), so a definition changes in one place only.
• Add a description to each measure; it appears as a tooltip in the field list.

Is DAX in demand in Morocco?

Yes: in data analyst, BI developer and financial controller job offers in Casablanca and Rabat, "Power BI" is often listed with "DAX" and "Power Query". Being able to explain CALCULATE and filter context in an interview sets a candidate apart from someone who only drags visuals. The PL-300 certification also explicitly tests these skills.

Conclusion

With SUM, DIVIDE, SUMX, CALCULATE and its filter modifiers, a handful of time intelligence functions, RANKX and variables, you can build most business KPIs in Power BI. Master filter context before memorising functions. To put these measures into pages, see the Power BI dashboard examples; for a project on your own data, see the Power BI dashboard service.