Optimizing DAX queries in Power BI - Real Business Scenario

Scenario Mode

👤 Your Role: You are a Power BI developer at a retail company.

📋 Request: Your manager wants you to improve the performance of slow Power BI reports by optimizing the DAX queries used in key measures.

📊 Data: You have sales data with columns: Date, Product, Region, SalesAmount, Quantity, and CustomerID. The data has about 100,000 rows.

🎯 Deliverable: Create optimized DAX measures that calculate Total Sales and Average Sales per Customer efficiently, and demonstrate the performance improvement with a simple report.

Sample Data

Date	Product	Region	SalesAmount	Quantity	CustomerID
2024-01-01	Widget A	North	100	2	C001
2024-01-02	Widget B	South	150	3	C002
2024-01-03	Widget A	East	200	4	C001
2024-01-04	Widget C	West	120	1	C003
2024-01-05	Widget B	North	180	3	C002
2024-01-06	Widget A	South	130	2	C004
2024-01-07	Widget C	East	170	3	C001
2024-01-08	Widget B	West	160	2	C003
2024-01-09	Widget A	North	140	3	C004
2024-01-10	Widget C	South	110	1	C002

1

Step 1: Create a basic Total Sales measure using SUM.

Total Sales = SUM(Sales[SalesAmount])

Expected Result

Total Sales sums all sales amounts correctly.

2

Step 2: Create a basic Average Sales per Customer measure using AVERAGEX and VALUES.

Average Sales per Customer = AVERAGEX(VALUES(Sales[CustomerID]), CALCULATE(SUM(Sales[SalesAmount])))

Expected Result

Calculates average sales amount per unique customer.

3

Step 3: Identify performance issue: AVERAGEX with VALUES can be slow on large data.

No formula, observation based on report slowness.

Expected Result

Report refresh is slow when using Average Sales per Customer measure.

4

Step 4: Optimize Average Sales per Customer by using DIVIDE and DISTINCTCOUNT to avoid row context iteration.

Average Sales per Customer Optimized = DIVIDE([Total Sales], DISTINCTCOUNT(Sales[CustomerID]), 0)

Expected Result

Faster calculation of average sales per customer without slow iteration.

5

Step 5: Create a report table visual with columns: Region, Total Sales, Average Sales per Customer Optimized.

Configure table visual in Power BI with fields: Region, Total Sales, Average Sales per Customer Optimized.

Expected Result

Report shows sales and average sales per customer by region with improved performance.

6

Step 6: Test report refresh time before and after optimization to confirm performance improvement.

No formula, measure report refresh time in Power BI.

Expected Result

Report refresh is noticeably faster after optimization.

Final Result

-----------------------------------------
| Region | Total Sales | Avg Sales/Customer |
-----------------------------------------
| North  | 420         | 140                |
| South  | 390         | 195                |
| East   | 370         | 370                |
| West   | 280         | 280                |
-----------------------------------------

✓Using DIVIDE with DISTINCTCOUNT is faster than AVERAGEX with VALUES for average calculations.

✓Optimized DAX measures improve report refresh time significantly.

✓Total Sales by region helps identify best performing areas quickly.

Bonus Challenge

Create an optimized DAX measure to calculate the running total of sales by date.

Show Hint

Use CALCULATE with FILTER and ALLSELECTED to accumulate sales up to the current date.