Advanced DAX and Performance: Variables, Rankings, Top N and New Customers | Free Power BI Course: From Beginner to Advanced

What you'll learn

Write clearer, faster measures with variables (VAR and RETURN).
Build rankings, Top N calculations and customer segments.
Count new customers and use inactive relationships with USERELATIONSHIP.
Diagnose and speed up a slow report.
Apply rankings, a Top 5 and segments to Summit's 12 products and 40 customers.

With CALCULATE, filter context and time intelligence you can already handle most reports. This lesson adds the techniques professionals reach for in the hard cases, and shows you how to find out why a report is slow.

66.9%of Summit's sales come from its top 5 products

9 of 40customers make up segment A (more than $15,000 in purchases in 2025–2026)

37of the 40 customers had already ordered in January 2025: the customer base barely grows

Variables: VAR and RETURN

Variables store an intermediate result under a name. They make measures easier to read, avoid calculating the same thing twice and make bugs easier to find:

YoY % =
VAR CurrentSales = [Total Sales]
VAR PriorSales   = [Sales PY]
RETURN
    IF (
        NOT ISBLANK ( PriorSales ),
        DIVIDE ( CurrentSales - PriorSales, PriorSales )
    )

A variable is evaluated in the context where it's defined, and its value doesn't change afterward, even if you use it inside a CALCULATE.
To debug, temporarily change RETURN to one of the variables and you'll see its value in the visual.

Rankings with RANKX

Product Rank =
IF (
    ISINSCOPE ( Products[Product] ),
    RANKX ( ALL ( Products[Product] ), [Total Sales] )
)

ALL(Products[Product]): the full list of products to compare against (if you used only the visible ones, each row would be compared with itself and everything would rank 1).
ISINSCOPE: prevents a meaningless rank on the total row.
With the Lesson 6 sample table: Rice ($162) ranks 1st, Oil ($117) 2nd and Sugar ($45.60) 3rd.
If the user filters a city, the rank recalculates with that city's sales, because ALL only removes the filter on the Product column.

Top N: sales from the top 5 products

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

% Sales Top 5 =
DIVIDE ( [Sales Top 5 Products], [Total Sales] )

TOPN returns a table with the 5 best-selling products in the current context, which CALCULATE uses as a filter. "% Sales Top 5" measures concentration: if 5 products make 70% of sales, the business depends heavily on them. To show only the top 10 in a chart, you don't need DAX: use the visual's Top N filter (Lesson 3).

At Summit, the top five products add up to $344,957.93, or 66.9% of sales:

Top 5 products: % of 2025–2026 sales

1 · Rice 20 lb17.6%

2 · Milk 4-pack15.8%

3 · Coffee14.3%

4 · Cheddar11.4%

5 · Vegetable oil7.8%

Two out of every three dollars depend on five products. A stockout of rice or milk has a huge impact; one of spaghetti (last in the ranking, at 2.1%), very little. You'll come back to this with ABC analysis in Lesson 15.

1. Your ranking measure shows "1" on every row of the table. What's the most likely cause?

RANKX runs over the table filtered by the row (each product is compared only with itself); you need ALL(Products[Product]).
Every product sells the same amount.
The date table is missing.

RANKX's first argument must be the complete list of competing items, without the row's filter.

Segment customers with SWITCH(TRUE())

Classify customers by their historical purchases with a calculated column in the Customers table (so you can use the segment in slicers and on axes):

Segment =
VAR CustomerSales = [Total Sales]
RETURN
    SWITCH (
        TRUE (),
        CustomerSales >= 15000, "A · Large",
        CustomerSales >= 11000, "B · Mid-size",
        CustomerSales > 0,      "C · Small",
        "No purchases"
    )

SWITCH(TRUE(), …) checks the conditions in order and returns the result of the first one that's true: it's DAX's version of Excel's IFS. Because the measure [Total Sales] is used inside a calculated column, context transition (Lesson 7) makes it calculate each customer's sales. Remember that a calculated column is recalculated when data refreshes, not when users filter: the segment is fixed for the loaded period.

Thresholds should come from your data, not from someone else's report. At Summit the smallest customer bought $7,260.65 and the largest $17,733.02; with cutoffs at $15,000 and $11,000 you get 9 A customers, 23 B and 8 C. Here's how sales split:

Segment A: $144,657.51 (9 customers); B: $291,358.48 (23); C: $79,953.04 (8). Together: $515,969.03.

New customers

A customer is new in a period if their first purchase ever falls inside that period:

New Customers =
VAR PeriodStart = MIN ( 'Calendar'[Date] )
VAR CustomersWithFirstPurchase =
    ADDCOLUMNS (
        VALUES ( Sales[CustomerID] ),
        "@FirstPurchase", CALCULATE ( MIN ( Sales[OrderDate] ), REMOVEFILTERS ( 'Calendar' ) )
    )
RETURN
    COUNTROWS ( FILTER ( CustomersWithFirstPurchase, [@FirstPurchase] >= PeriodStart ) )

VALUES ( Sales[CustomerID] )Who bought in the periodThe list of customers with sales in the month, quarter or year in context.
CALCULATE + REMOVEFILTERS ( 'Calendar' )When was their first purchaseFor each customer, the earliest date in their entire history, ignoring the date filter.
FILTER + COUNTROWSHow many are newCounts those whose first purchase is on or after the start of the period.

At Summit, the measure by month returns 37 new customers in January 2025, 2 in February, 1 in March and zero ever since: all 40 customers have been buying since the first quarter. That's not a bug, it's a finding: the distributor grows by selling more to the same customers, so the metric to watch is retention (lost customers), not acquisition.

With this measure, "Returning Customers" is simply [Customers] - [New Customers].

Inactive relationships: USERELATIONSHIP

If Sales had both an OrderDate and a ShipDate, only one relationship to Calendar could be active (Lesson 5). The other stays inactive (a dashed line) and you activate it inside a measure:

Sales by Ship Date =
CALCULATE (
    [Total Sales],
    USERELATIONSHIP ( Sales[ShipDate], 'Calendar'[Date] )
)

2. Why does New Customers use REMOVEFILTERS('Calendar') when looking up the first purchase?

To make the measure faster.
To find the customer's first purchase in their entire history, not just the first one inside the filtered period.
Because the Calendar table has errors.

Without removing the date filter, the "first purchase" would always fall inside the period and every customer would look new.

Performance: when a report is slow

Step 1: measure

Open View › Performance analyzer, click Start recording and then Refresh visuals. You'll see how many milliseconds each visual takes, split into DAX query, visual display and other. Tackle the slowest ones first. For deeper analysis there's the free external tool DAX Studio.

Step 2: apply the highest-impact fixes

Problem	Fix
Large model	Remove unused columns and rows in Power Query; every column uses memory
High-cardinality columns (transaction IDs, date-times with seconds)	Drop them if you don't need them; split date and time; round unnecessary decimals
One giant table with everything	A star schema (Lesson 5)
Bidirectional relationships	Use them only when truly necessary
FILTER over entire fact tables	Filter columns or dimensions; use simple filters in CALCULATE
Measures that calculate the same thing several times	Variables
Too many visuals per page	Fewer visuals, more tooltips and drillthrough (Lesson 9)
Complex calculated columns	Move them to Power Query or the source when possible

Import or DirectQuery

In Import mode (the default), data is copied and compressed into the model: very fast, but it updates on a schedule. In DirectQuery, every click queries the source database in real time: always current, but slower and with some DAX limitations. Choose DirectQuery only when you need near-real-time data or the volume is huge. Lesson 14 covers this in depth.

3. Performance analyzer shows a matrix spending 9 seconds on the DAX query. What do you check first?

The theme colors.
The font size of the titles.
The measures it uses: FILTER over big tables, repeated calculations and bidirectional relationships.

If the time is in the DAX query, the problem is in the measures or the model, not the visual design.

4. At Summit, "% Sales Top 5" is 66.9% for the whole catalog. If the user filters the Beverages category, which has 4 products, what does the measure show?

66.9%, because TOPN uses ALL.
100%, because the top 5 within Beverages includes all 4 of its products.
An error, because there aren't 5 products.

ALL(Products[Product]) only removes the filter on the Product column; the category filter still applies. Within Beverages the "top 5" covers every product, so concentration is 100%.

Common advanced DAX mistakes

RANKX over VALUES(Products[Product]), so everything ranks 1.

RANKX over ALL(Products[Product]) or ALLSELECTED, depending on the question.

Copying segment thresholds from another business ($10,000 / $3,000).

Look at your data's minimum, maximum and distribution and pick meaningful cutoffs.

Repeating [Sales PY] three times in the same measure.

Store it in a variable and use it as often as you like.

Concluding the new-customers measure "doesn't work" because it returns zero.

Validate against the data: at Summit, zero is the correct answer from April 2025 on.

Optimizing blindly by changing formulas at random.

Measure with Performance analyzer and fix the slowest visual first.

Practice exercise: advanced customer analysis

Rewrite "YoY %" with variables.
Create "Customer Rank" and a table of customers sorted by rank.
Create the Segment column (A, B, C) and a chart of sales by segment.
Create "New Customers" and "Returning Customers" and show them by month.
Run Performance analyzer on your report and identify the slowest visual.

See solution

Customer Rank follows the Product Rank pattern with ALL ( Customers[Customer] ); Thrifty Convenience 40 should rank first with $17,733.02. As a bonus, here's a lost-customers measure:

Lost Customers =
-- Bought in the 90 days before the period, but not during it
VAR PeriodStart = MIN ( 'Calendar'[Date] )
VAR CustomersBefore =
    CALCULATETABLE (
        VALUES ( Sales[CustomerID] ),
        DATESBETWEEN ( 'Calendar'[Date], PeriodStart - 90, PeriodStart - 1 )
    )
VAR CustomersNow = VALUES ( Sales[CustomerID] )
RETURN
    COUNTROWS ( EXCEPT ( CustomersBefore, CustomersNow ) )

EXCEPT returns the rows of the first table that aren't in the second. This measure powers the "Customers who stopped ordering" page you planned in Lesson 4.

Summary

Variables make measures clearer, faster and easier to debug.
RANKX with ALL builds rankings; TOPN measures the weight of the top N.
SWITCH(TRUE()) assigns segments; REMOVEFILTERS lets you find first purchases and new customers.
USERELATIONSHIP activates inactive relationships inside a measure.
Measure with Performance analyzer and simplify the model before blaming Power BI.
Choose thresholds and validate results against your data: at Summit, 5 products make 66.9% of sales and no new customers have arrived since March 2025.

Next lesson: Final Project: Build a Complete Sales Dashboard in Power BI. You'll pull everything together into Summit Supply's complete sales dashboard, from raw data to a published report.