Optimizing DAX queries in Power BI - Dashboard Guide

Dashboard Mode - Optimizing DAX queries

Goal

Understand how to write efficient DAX formulas to improve Power BI report performance.

Sample Data

OrderID	Product	Category	Quantity	UnitPrice	OrderDate
1001	Notebook	Stationery	5	2.5	2024-01-10
1002	Pen	Stationery	10	1.2	2024-01-11
1003	Chair	Furniture	2	45	2024-01-12
1004	Desk	Furniture	1	120	2024-01-13
1005	Marker	Stationery	7	1.5	2024-01-14
1006	Bookcase	Furniture	1	80	2024-01-15

Dashboard Components

• KPI Card: Total Sales
  Formula: Total Sales = SUMX(Sales, Sales[Quantity] * Sales[UnitPrice])
  Result: 5*2.5 + 10*1.2 + 2*45 + 1*120 + 7*1.5 + 1*80 = 12.5 + 12 + 90 + 120 + 10.5 + 80 = 325
• KPI Card: Total Quantity Sold
  Formula: Total Quantity = SUM(Sales[Quantity])
  Result: 5 + 10 + 2 + 1 + 7 + 1 = 26
• Bar Chart: Sales by Category
  Formula for measure: Sales by Category = SUMX(FILTER(Sales, Sales[Category] = SELECTEDVALUE(Sales[Category])), Sales[Quantity] * Sales[UnitPrice])
  Values:
  - Stationery: (5*2.5)+(10*1.2)+(7*1.5) = 12.5 + 12 + 10.5 = 35
  - Furniture: (2*45)+(1*120)+(1*80) = 90 + 120 + 80 = 290
• Table: Sales Details
  Columns: OrderID, Product, Category, Quantity, UnitPrice, Total Sale
  Calculated Column Formula: Total Sale = Sales[Quantity] * Sales[UnitPrice]
  Shows each order's total sale value.
• Optimized Measure: Total Sales Optimized
  Formula: Total Sales Optimized = SUMX(Sales, Sales[Quantity] * Sales[UnitPrice])
  Note: Using SUMX directly on the Sales table is efficient here because it avoids unnecessary FILTER or CALCULATE functions.

Dashboard Layout

+----------------------+----------------------+
|      Total Sales      |  Total Quantity Sold  |
|        (KPI)          |        (KPI)          |
+----------------------+----------------------+
|                                              |
|           Sales by Category (Bar Chart)      |
|                                              |
+----------------------------------------------+
|                                              |
|               Sales Details (Table)           |
|                                              |
+----------------------------------------------+

Interactivity

Adding a slicer for Category filters all components:

• KPI cards update to show totals only for the selected category.
• Bar chart highlights the selected category's sales.
• Sales details table shows only orders from the selected category.

This interactivity helps users focus on specific product categories and see optimized DAX measures update instantly.

Self Check

If you add a filter for Category = Furniture, which components update and what are their new values?

• Total Sales: 290
• Total Quantity Sold: 4 (2+1+1)
• Sales by Category Bar Chart: Only Furniture bar shows 290, Stationery bar is hidden.
• Sales Details Table: Shows only orders 1003, 1004, 1006.

Key Result

Dashboard shows total sales and quantity with category breakdown and detailed sales table using optimized DAX formulas.

Practice

1.

What is a simple way to improve the performance of a DAX query in Power BI?

SUM(Sales[Amount]) is slow. What should you do?

easy

A. Avoid using filters in the query

B. Add more columns to the table

C. Use complex nested IF statements

D. Use variables to store intermediate results

Solution

1. Step 1: Understand the role of variables in DAX

   Variables store intermediate results to avoid repeated calculations, making queries faster.

2. Step 2: Apply variables to the example

   Using variables in SUM(Sales[Amount]) can reduce repeated scans and improve speed.

3. Final Answer:

   Use variables to store intermediate results -> Option D

4. Quick Check:

   Variables improve performance [OK]

Hint: Use variables to avoid repeated calculations [OK]

Common Mistakes:

• Adding unnecessary columns slows down queries
• Using complex nested IFs increases calculation time
• Ignoring filters can cause large data scans

2.

Which of the following is the correct syntax to define a variable in a DAX measure?

Measure = ?

easy

A. Measure = DEFINE Total = SUM(Sales[Amount]) USE Total

B. Measure = VAR Total = SUM(Sales[Amount]) RETURN Total

C. Measure = LET Total = SUM(Sales[Amount]) THEN Total

D. Measure = SET Total = SUM(Sales[Amount]) OUTPUT Total

Solution

1. Step 1: Recall DAX variable syntax

   DAX uses VAR to declare variables and RETURN to output the result.

2. Step 2: Match syntax with options

   Measure = VAR Total = SUM(Sales[Amount]) RETURN Total correctly uses VAR Total = SUM(Sales[Amount]) RETURN Total.

3. Final Answer:

   Measure = VAR Total = SUM(Sales[Amount]) RETURN Total -> Option B

4. Quick Check:

   VAR and RETURN are correct DAX keywords [OK]

Hint: Remember: VAR ... RETURN in DAX for variables [OK]

Common Mistakes:

• Using LET or DEFINE instead of VAR
• Forgetting RETURN keyword
• Using SET or OUTPUT which are not DAX keywords

3.

Consider this DAX measure:

Measure =
VAR FilteredSales = FILTER(Sales, Sales[Region] = "West")
RETURN SUMX(FilteredSales, Sales[Amount])

What is the main performance issue with this measure?

medium

A. FILTER creates a filtered table and SUMX iterates over it, which can be slow if Sales is large

B. FILTER is applied before SUMX, which is efficient

C. FILTER creates a filtered table but SUMX uses the original table, causing repeated scans

D. FILTER creates a table but SUMX iterates over original Sales table

Solution

1. Step 1: Analyze FILTER and SUMX behavior

   FILTER creates a filtered table of Sales where Region is "West". SUMX iterates over this filtered table.

2. Step 2: Understand performance impact

   If Sales is large, creating and iterating over a filtered table can be slow. Better to filter early or use CALCULATE with SUM.

3. Final Answer:

   FILTER creates a filtered table and SUMX iterates over it, which can be slow if Sales is large -> Option A

4. Quick Check:

   FILTER + SUMX on large tables can slow queries [OK]

Hint: Avoid FILTER + SUMX on big tables; use CALCULATE [OK]

Common Mistakes:

• Thinking SUMX iterates original table, not filtered
• Assuming FILTER before SUMX is always fast
• Ignoring table size impact on performance

4.

Identify the error in this DAX measure that aims to optimize performance:

Measure =
VAR TotalSales = SUM(Sales[Amount])
RETURN
CALCULATE(TotalSales, FILTER(Sales, Sales[Region] = "East"))

medium

A. Using FILTER inside CALCULATE is always slow

B. TotalSales should be inside FILTER function

C. TotalSales is a scalar evaluated outside CALCULATE, so the filter does not apply

D. No error; measure is optimized

Solution

1. Step 1: Check variable type and usage

   TotalSales = SUM(Sales[Amount]) is a scalar value computed in the original filter context.

2. Step 2: Analyze CALCULATE arguments

   CALCULATE(TotalSales, FILTER(Sales, Sales[Region]="East")) is syntactically valid, but since TotalSales is a pre-computed scalar, the modified filter context does not re-evaluate it. It returns the unfiltered total instead of East sales, defeating the performance optimization goal for filtered calculation.

3. Final Answer:

   TotalSales is a scalar evaluated outside CALCULATE, so the filter does not apply -> Option C

4. Quick Check:

   Scalar variable used incorrectly with CALCULATE filter [OK]

Hint: Scalar variables computed outside aren't affected by CALCULATE filters [OK]

Common Mistakes:

• Thinking CALCULATE re-evaluates scalar variables in new context
• Thinking FILTER inside CALCULATE is always slow
• Not distinguishing scalar vs table types

5.

You want to optimize a DAX measure that calculates total sales for the current year only. Which approach is best for performance?

Option 1:
Measure = CALCULATE(SUM(Sales[Amount]), FILTER(Sales, YEAR(Sales[Date]) = YEAR(TODAY())))

Option 2:
Measure = CALCULATE(SUM(Sales[Amount]), Sales[Date].[Year] = YEAR(TODAY()))

hard

A. Option 2 is better because it uses column filter, not row filter

B. Option 1 is better because FILTER is more flexible

C. Both options have same performance

D. Neither option works; use SUMX instead

Solution

1. Step 1: Compare FILTER vs direct column filter

   Option 1 uses FILTER which scans row by row, applying YEAR function, which is slow.

2. Step 2: Understand column filter advantage

   Option 2 uses a direct filter on the date column's Year attribute, which is faster because it uses existing column indexing and avoids row-by-row evaluation.

3. Final Answer:

   Option 2 is better because it uses column filter, not row filter -> Option A

4. Quick Check:

   Use column filters over FILTER for better speed [OK]

Hint: Filter on columns, not rows, for faster DAX queries [OK]

Common Mistakes:

• Using FILTER with row context unnecessarily
• Assuming FILTER is always faster
• Ignoring built-in column filters