Ravindra BagaleCourses & study guides मराठी Track your progress

27. Performance Tips

Practice and Answer Key: Performance Tuning

By Last updated:

Suppose we are fixing the Blinkit Maharashtra report that takes 40 seconds to open. This is a paper exercise first; then try the same steps on your own file. Model facts (fictional practice data):

Table · Column Rows Different values Used in a visual?
Orders · Order ID 5,00,000 5,00,000 Only inside Total Orders
Orders · Order DateTime 5,00,000 about 4,96,000 Only for the date axis
Orders · Delivery Notes (free text) 5,00,000 about 3,10,000 No
Orders · Amount 5,00,000 about 4,200 Yes
Orders · Store ID 5,00,000 24 Yes (relationship)

Performance Analyzer (page total 40,000 ms):

Visual DAX query (ms) Visual display (ms) Other (ms) Total (ms)
Table: all orders 28,400 2,500 300 31,200
Map: stores 600 7,400 200 8,200
Bar: Sales by city 260 140 20 420
Card: Total Sales 120 50 10 180

A measure in the model: Late Orders = COUNTROWS(FILTER(Orders, Orders[Delivery Time Mins] > 10))

Exercise (one under the other):

  1. Which column do you remove completely? Which column do you split?
  2. Which visual do you fix first, and is it a DAX problem or a display problem?
  3. Which visual is a display problem?
  4. Rewrite Late Orders so it filters one column, not the whole table.
  5. The DarkStore → Orders relationship is set to Both. What do you change?

Expected answers (check yourself):

# Answer
1 Remove Delivery Notes (3,10,000 different values, never used). Split Order DateTime into Order Date (365 values) and Order Time to the minute (1,440). Keep Order ID: Total Orders needs it
2 Table: all orders — 31,200 of 40,000 ms, mostly DAX query (28,400). Show Top 100 rows and 5 columns; put full detail on a drill-through page
3 Map: stores — 7,400 ms of visual display. Use latitude and longitude, fewer bubbles
4 Late Orders = CALCULATE(COUNTROWS(Orders), Orders[Delivery Time Mins] > 10)
5 Set the cross-filter direction to Single (DarkStore filters Orders)
Answer key notes (open after you try)
  • Total before = 31,200 + 8,200 + 420 + 180 = 40,000 ms.
  • FILTER(Orders, …) walks every row and every column of Orders. A simple column filter inside CALCULATE lets the engine use just one column.
  • Measure the change: record in Performance Analyzer, change one thing, record again.
  • Use DAX query view (Copy query from Performance Analyzer) to test a slow measure on its own.

Common mistakes:

What you see Why Fix
File still big after removing columns Auto date/time is on, so hidden date tables were made for every date column File › Options › Data load › untick Auto date/time
Page is fast in Desktop, slow in the Service Desktop cached results; DirectQuery or gateway is slow Test in the Service and check the source
Changed five things, page got slower No idea which change hurt One change per recording

Samajla ka? Measure first, fix the biggest number, measure again.

Thodkyaat sangaycha tar (quick recap)

Measure first, then fix. Reduce columns and cardinality (how many different values a column has); keep the star schema; and do not put too many visuals on one page. Now let us move on – let us build a full project!