TECH **DAX Basics: SUM, CALCULATE, FILTER, ALL, RELATED – Zero-Fluff Study Guide**

DAX Basics: SUM, CALCULATE, FILTER, ALL, RELATED – Zero-Fluff Study Guide

(For Power BI Developers & PL-300 Exam Prep)

1. What This Is & Why It Matters

DAX (Data Analysis Expressions) is Power BI’s formula language—think of it as Excel on steroids, but for relational data models. If you’ve ever: - Built a report that "just won’t calculate right" (e.g., sales totals ignoring filters, incorrect YoY growth), - Struggled with time intelligence (e.g., "Why does my MTD measure show the same as YTD?"), - Inherited a messy Power BI file where measures return nonsense when slicers change,

…then DAX is your lifeline.

Real-world scenario:
You’re handed a Power BI report where: - A "Total Sales" measure works in a table but breaks when a user filters by region.
- A "Prior Year Sales" measure returns blank for some months.
- A "Top 5 Products" table shows duplicates because the data model has no relationships.

Without DAX, you’re stuck with static aggregations or manual Excel fixes. With DAX, you control how filters propagate, override context, and dynamically reshape data—without touching the source.

2. Core Concepts & Components

? SUM

• Definition: Adds up values in a column.
• Production insight: SUM is the simplest aggregation, but it respects filter context (e.g., if a user filters to "West Region," SUM(Sales[Amount]) only sums West sales). If you need to ignore filters, pair it with CALCULATE + ALL.

? CALCULATE

• Definition: The most powerful DAX function—modifies filter context for a calculation.
• Production insight: Without CALCULATE, measures like "Sales Last Year" or "Sales vs. Target" are impossible. It’s the only function that can override filters (e.g., CALCULATE(SUM(Sales[Amount]), ALL(Sales)) ignores all filters on Sales).

? FILTER

• Definition: Returns a subset of a table based on a condition (e.g., FILTER(Sales, Sales[Amount] > 1000)).
• Production insight: FILTER is row-by-row, so it’s slow on large tables. Use it only when necessary—prefer CALCULATE + Boolean conditions (e.g., CALCULATE(SUM(Sales[Amount]), Sales[Amount] > 1000)).

? ALL

• Definition: Removes filters from a table or column (e.g., ALL(Sales) removes all filters on Sales).
• Production insight: Critical for ratios (e.g., "Sales % of Total") and time intelligence (e.g., "Sales vs. All-Time Average"). Overusing ALL can break slicer interactions—test thoroughly.

? RELATED

• Definition: Fetches a value from a related table (e.g., RELATED(Product[Category]) pulls the product category into the Sales table).
• Production insight: Only works if there’s a relationship in the data model. If RELATED returns blank, check your relationships (or use LOOKUPVALUE as a fallback).

3. Step-by-Step Hands-On: Build a Dynamic Sales Dashboard

Prerequisites

• Power BI Desktop installed.
• A dataset with:
• Sales table (columns: Date, ProductID, Amount, Region).
• Products table (columns: ProductID, Category, Cost).
• A relationship between Sales[ProductID] and Products[ProductID].

Task: Create 4 Measures

1. Total Sales (basic aggregation).
2. Sales % of Total (ignores filters).
3. Sales vs. Prior Year (time intelligence).
4. Top 5 Products by Profit (dynamic filtering).

Step 1: Total Sales

Total Sales = SUM(Sales[Amount])

• Why it works: Sums Amount in the current filter context (e.g., if a user filters to "2023," it only sums 2023 sales).
• Verify: Add a table visual with Date and Total Sales. Filter by year—values should update.

Step 2: Sales % of Total

Sales % of Total =
DIVIDE(
    [Total Sales],
    CALCULATE([Total Sales], ALL(Sales))
)

• Why it works:
• CALCULATE([Total Sales], ALL(Sales)) ignores all filters on Sales, giving the grand total.
• DIVIDE handles division by zero (returns blank if no sales).
• Verify: Add a slicer for Region. The % of Total should stay the same regardless of region (because ALL(Sales) removes the region filter).

Step 3: Sales vs. Prior Year

Sales PY =
CALCULATE(
    [Total Sales],
    SAMEPERIODLASTYEAR(Sales[Date])
)

• Why it works:
• SAMEPERIODLASTYEAR shifts the date filter back 1 year.
• CALCULATE applies this new filter context to Total Sales.
• Verify: Add a line chart with Date (by month) and both [Total Sales] and [Sales PY]. The PY line should lag by 12 months.

Step 4: Top 5 Products by Profit

Top 5 Products =
VAR TopProducts =
    TOPN(
        5,
        SUMMARIZE(
            Sales,
            Products[ProductName],
            "Profit", SUM(Sales[Amount]) - SUM(Products[Cost])
        ),
        [Profit]
    )
RETURN
    CONCATENATEX(
        TopProducts,
        Products[ProductName] & ": $" & FORMAT([Profit], "0"),
        UNICHAR(10)
    )

• Why it works:
• SUMMARIZE creates a table of ProductName and Profit.
• TOPN(5, ..., [Profit]) keeps only the top 5 products by profit.
• CONCATENATEX formats the result as a multi-line string.
• Verify: Add a card visual with [Top 5 Products]. It should show the top 5 products dynamically (e.g., if you filter by region, the list updates).

4. ? Production-Ready Best Practices

Performance

• Avoid FILTER on large tables: Use CALCULATE + Boolean conditions instead.
  ```dax // ❌ Slow (row-by-row) CALCULATE(SUM(Sales[Amount]), FILTER(Sales, Sales[Amount] > 1000))

// ✅ Fast (columnar) CALCULATE(SUM(Sales[Amount]), Sales[Amount] > 1000) `` - UseSUMMARIZEsparingly: It’s slow—preferGROUPBYorSUMMARIZECOLUMNS` for aggregations.

Maintainability

• Name measures clearly: [Total Sales] > [Sum of Sales Amount].
• Comment complex measures:
  dax // Calculates YoY growth, ignoring region filters Sales YoY Growth = VAR CurrentSales = [Total Sales] VAR PriorSales = CALCULATE([Total Sales], SAMEPERIODLASTYEAR('Date'[Date])) RETURN
DIVIDE(CurrentSales - PriorSales, PriorSales)

Debugging

• Use VAR to break down logic: Makes measures easier to debug.
• Check filter context: Add a SELECTEDVALUE measure to see active filters.
  dax Debug Filter = "Region: " & SELECTEDVALUE(Sales[Region], "All")

5. ⚠️ Common Mistakes & Traps

6. ? Exam/Certification Focus (PL-300)

Question Patterns

1. "Which function ignores filters?"
2. ✅ ALL, ALLEXCEPT, ALLSELECTED

3. ❌ FILTER, SUM, RELATED

4. "How do you calculate % of total?"

5. ✅ DIVIDE([Measure], CALCULATE([Measure], ALL(Table)))

6. ❌ DIVIDE([Measure], SUM(Table[Column])) (respects filters)

7. "Why does RELATED return blank?"

8. ✅ Missing relationship or inactive relationship.
9. ❌ "The column doesn’t exist" (Power BI would error).

Key Trap Distinctions

• ALL vs. ALLSELECTED:
• ALL removes all filters (including slicers).
• ALLSELECTED removes filters but keeps visual-level filters (e.g., a table’s filter).
• FILTER vs. CALCULATE + Boolean:
• FILTER is row-by-row (slow).
• CALCULATE + Boolean is columnar (fast).

Scenario-Based Question

"You need to show 'Sales % of Region Total' in a table. Users filter by year, but the % should always be relative to the region’s total (ignoring year). Which DAX measure works?"

✅ Answer:

Sales % of Region =
DIVIDE(
    [Total Sales],
    CALCULATE([Total Sales], ALL('Date'))
)

• Why? ALL('Date') removes the year filter, but keeps the region filter (since ALL isn’t applied to Sales[Region]).

7. ? Hands-On Challenge

Challenge:
Create a measure that shows "Sales vs. Category Average" (e.g., "This product’s sales are 120% of its category average").

Solution:

Sales vs Category Avg =
VAR CurrentSales = [Total Sales]
VAR CategoryAvg =
    CALCULATE(
        AVERAGE(Sales[Amount]),
        ALL(Products[ProductName])  // Remove product filter, keep category
    )
RETURN
    DIVIDE(CurrentSales, CategoryAvg)

• Why it works:
• ALL(Products[ProductName]) removes the filter on the current product but keeps the category filter.
• AVERAGE(Sales[Amount]) calculates the average sales for all products in the same category.

8. ? Rapid-Reference Crib Sheet

9. ? Where to Go Next

1. Official Docs:
2. DAX Reference (Microsoft)
3. DAX Guide (SQLBI) (best for deep dives)
4. Books:
5. The Definitive Guide to DAX (Marco Russo & Alberto Ferrari) – The bible of DAX.
6. Practice:
7. DAX.do (online DAX playground).
8. AdventureWorks sample dataset (for real-world practice).