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.