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

27. Performance Tips

27.5 DAX Best Practices

By Last updated:

Start with a problem

A measure using FILTER(Orders, ...) on the whole table is slow.

Explain simply

Use simple filter arguments in CALCULATE, variables, DIVIDE, and avoid iterating big tables unnecessarily.

Show the data

City Total Sales (₹)
Pune 850
Nashik 800
Kolhapur 450
Total 2,100

About this data: This table uses columns: City, Total Sales (₹). This table uses columns: Measure, Result. This table uses columns: Wrong result, Reason, Correction. Values shown are the lesson examples already on this page (not a new dataset).

Fictional sample data. Practice file: visuals_orders_apr_may_2026.csv (source: src/practice/).

Demonstrate

  1. Replace CALCULATE([Total Sales], FILTER(Orders, Orders[City] = "Pune")) with CALCULATE([Total Sales], Orders[City] = "Pune").
  2. Use VAR for repeated expressions.
  3. Compare in Performance Analyzer.

Show the result

Your result should look like this (fictional sample):

Measure Result
Both versions ₹850 (same value, simpler query)

Explain what happened

A column filter lets the engine use the column directly instead of scanning all rows.

Explore further

  • Use DAX query view to test.

Detailed reference for this lesson:

  • Measures over calculated columns for aggregations – measures are computed only when needed and take no storage.
  • Use variables to avoid calculating the same expression twice.
  • Use DIVIDE instead of IF(denominator = 0, …) patterns.
  • Filter columns, not tables: CALCULATE([Total Sales], Product[Category] = "Snacks") rather than FILTER(Orders, RELATED(Product[Category]) = "Snacks").
  • Avoid iterating large tables with complex row-by-row logic when a simple aggregation works.
  • Reduce use of FORMAT in measures that feed large tables.
  • Return BLANK instead of 0 where appropriate – visuals then skip empty combinations rather than showing thousands of zero rows.

Ravindra Bagale's Tip

A common DAX performance mistake is using FILTER over an entire fact table, and repeating sub-expressions. Use column filters inside CALCULATE, store repeated values in variables, and prefer DIVIDE. Test a slow measure in DAX Studio to see the server timings. It's very simple – just make it a habit.

Fix common mistakes

Wrong result Reason Correction
Different values after rewrite FILTER on table kept other filters differently Test values before/after
Repeated measure calls No variables Use VAR
Errors from division / DIVIDE

Practise independently

Practice task

What should both versions return for Pune?

Hints
  • City total.
Solution

₹850.

Think like a professional

Always prove a rewrite returns the same number.

Loading DAX practice tasks...

  1. Total Parcels (Easy): Measure: Parcels Sum = SUM of Parcels.
  2. On-Time Rate (Medium): Shown per Hubs Name. Measure: On-Time Rate = SUM of OnTime ÷ SUM of Parcels.
  3. On-Time Rate for Air (Hard): Shown per Country. Measure: On-Time Rate Air = On-Time Rate where Mode is "Air".
  4. On-Time Rate against target (Hard): Shown per Hubs Name. Measure: On-Time Rate Status = "On target" when On-Time Rate is >= 0.9, else "Check".
  5. Parcels for Air only (Medium): Shown per City. Measure: Parcels Air = SUM of Parcels where Mode is "Air".
  6. Rows with high damaged parcels (Medium): Shown per Country. Measure: High Damaged Rows = number of rows where Damaged is more than 15.
  7. Average Parcels per row (Medium): Shown per Hubs Name. Measure: Parcels per Row = SUM of Parcels ÷ number of rows.
  8. Share of Parcels (Medium): Shown per Hubs Name. Measure: Parcels Share = SUM of Parcels ÷ SUM of Parcels for all of Hubs.
  9. Parcels minus OnTime (Hard): Shown per Mode. Measure: Gap = SUMX over Shipments of Parcels − OnTime.
  10. Parcels in the first half-year (Medium): Shown per Country. Measure: H1 Parcels = SUM of Parcels for ShipDate before 1 Oct 2025.

See all Power BI practice tasks