Filter columns, not tables, in DAX

Summary

In this SQLBI video, the speaker emphasizes the importance of adhering to DAX best practices, particularly the rule against using tables as filters. The video demonstrates the pitfalls of this approach, showing how it can lead to incorrect results and slow performance. By correcting the measure to filter columns instead, the speaker illustrates the significant improvement in both accuracy and speed. The explanation delves into the concept of expanded tables and their impact on DAX performance, offering insights to help developers avoid common mistakes and enhance their DAX coding skills.

Takeaways

• 🚫 Never use tables as filters in DAX as it leads to incorrect and slow calculations.
• 📉 Filtering tables can result in significantly lower numbers due to the intersection of expanded tables.
• 🔍 The 'expanded table' concept in DAX is crucial for understanding performance issues and correct measure calculations.
• 🛠️ Correct practice is to filter on columns rather than tables to ensure accurate and efficient DAX measures.
• ⏱️ Filtering on tables can cause the calculation process to be much slower due to the way data is retrieved and intersected.
• 🔢 The script demonstrates the difference in performance between incorrect (filtering tables) and correct (filtering columns) DAX measure implementations.
• 📈 A correct DAX measure should reflect the sum of conditions across both sales and receipts without unnecessary intersections.
• 💡 Understanding the expanded table's impact on DAX measures is essential for debugging and optimizing performance.
• 📊 The video provides a practical example using a modified version of a DAX model to illustrate the concept of expanded tables.
• 👨‍🏫 For beginners, the mistake of filtering tables is common, but it's a critical concept to master for effective DAX development.
• 💻 The use of tools like Profiler in DAX Studio can help visualize and understand the performance implications of expanded tables.

Q & A

Why should tables not be used as filters in the CALCULATE function in DAX?

Tables should not be used as filters in the CALCULATE function because doing so can lead to incorrect results and significantly slower performance. This is due to the complexity of expanded tables, which the engine has to process, leading to inefficient calculations.

What happens when you filter tables instead of columns in DAX?

When you filter tables instead of columns in DAX, the engine needs to retrieve and intersect all combinations of related tables. This results in incorrect numbers due to unintended intersections and a dramatic decrease in performance due to the complexity of processing expanded tables.

What is the correct approach to filtering in DAX, as discussed in the video?

The correct approach to filtering in DAX is to apply filters directly to columns rather than entire tables. This avoids unnecessary intersections of expanded tables and ensures that the calculations are both accurate and efficient.

What are 'expanded tables' in the context of DAX, and why are they important?

Expanded tables in DAX include not only the columns of the base table but also the columns of all related tables. They are important because they influence how filters and calculations interact, potentially leading to performance issues and incorrect results if not properly understood and managed.

How does filtering on tables affect the speed of DAX calculations?

Filtering on tables greatly slows down DAX calculations because the engine must process all related tables in the expanded version of the table. This increases the computational complexity and results in longer query execution times.

Why does the video recommend using KEEPFILTERS in certain DAX calculations?

The video recommends using KEEPFILTERS to maintain the intended filtering context when filtering columns. This ensures that the calculations remain correct by preserving existing filters instead of overriding them.

What are the consequences of incorrectly filtering tables in DAX, as shown in the demo?

The consequences of incorrectly filtering tables in DAX include incorrect and misleading results, as demonstrated by the large discrepancy in calculated revenues in the demo. It also causes significant performance degradation, making the calculations take much longer to complete.

What are the key differences between the 'large revenues wrong' and 'large revenues correct' measures in the video?

The key differences between 'large revenues wrong' and 'large revenues correct' are in how they apply filters. The wrong version filters entire tables, causing expanded table intersections and slow performance. The correct version filters specific columns, leading to accurate results and much faster calculations.

How does the video suggest debugging performance issues in DAX measures?

The video suggests using tools like DAX Studio to analyze the query plan and server timings. This allows you to see how the engine processes the calculations, identify where time is spent, and understand the impact of expanded tables on performance.

What is the overall lesson the video conveys about writing efficient DAX code?

The overall lesson is to avoid filtering entire tables and instead focus on filtering specific columns. This practice prevents performance bottlenecks and ensures accurate results, helping developers write more efficient and reliable DAX code.