Notes on DAX Best Practices: Why You Should Never Use Tables as Filters in CALCULATE

Introduction

• Welcome to SQL BI presentation on DAX best practices.
• Focus: Importance of using filter columns instead of filter tables in DAX CALCULATE function.
• Emphasis on one of the golden rules in DAX.

Key Rule

• Filter Columns Never Filter Tables
  • A simple rule to follow for effective DAX calculations.
  • Understanding the rationale behind this rule is crucial for better DAX usage.

Demo Overview

1. Model Setup

   • Using a modified version of Contoso with two fact tables: Sales and Receipts.
   • Sales represent products sold to known customers through orders.
   • Receipts represent direct sales without customer information.
   • Revenue comes from both sources, and they need to be analyzed separately.

2. Initial Measures

   • Sales Amount: SUMX(Sales, Sales[Quantity] * Sales[Net Price])
   • Receipts Amount: SUMX(Receipts, Receipts[Quantity] * Receipts[Net Price])
   • Combined Revenues: Sum of Sales and Receipts amounts.

New Requirement: Calculate Large Revenues

• Aim: Calculate revenues for transactions over $500.
• Write a new measure Large Revenues using CALCULATE to filter for both Sales and Receipts.

Code Analysis of Incorrect Measure

• Attempt: Use filters on Sales and Receipts tables:

  CALCULATE(
      [Combined Revenues],
      FILTER(Sales, Sales[Quantity] * Sales[Net Price] >= 500),
      FILTER(Receipts, Receipts[Quantity] * Receipts[Net Price] >= 500)
  )
  

• Observations:
  • Results in slow performance (about 9 seconds) and incorrect value (1.5 million instead of expected).

Issues with Filtering Tables

• Filtering tables leads to a performance hit due to expanded tables.
• Expanded tables include all relationships, causing unnecessary complexity in calculations.
• The measure wrongly intersects the filters across tables, leading to a smaller result.

Correcting the Measure

1. Create Large Revenues Correct using individual column filters:

   CALCULATE(
       [Combined Revenues],
       Sales[Quantity] * Sales[Net Price] >= 500,
       Receipts[Quantity] * Receipts[Net Price] >= 500
   )
   

2. Results:
   • Correct calculation (9.3 million) and significantly faster (milliseconds).

Understanding Expanded Tables

• Expanded Table Concept:
  • Every table in DAX has an expanded version influenced by relationships.
  • Using filters on tables causes unnecessary computations across all related tables (intersecting filters).
• Performance Implications:
  • Slower calculations due to multiple storage engine queries.
  • Correct filtering leads to direct calculations in the storage engine.

Performance Testing with DAX Studio

• Execution of both correct and incorrect measures shows significant time differences:
  • Large Revenues Wrong: ~10 seconds.
  • Large Revenues Correct: ~53 milliseconds.
• Expanded tables lead to multiple storage engine queries in the wrong version.

Conclusion

• Key Takeaway: Always avoid filtering tables in DAX.
• Understanding expanded tables and their implications is crucial for optimizing DAX performance.
• New DAX developers should avoid using table filters to prevent debugging challenges and slow performance.
• Improve DAX code quality by using column filters instead of table filters.