Lecture on DAX and Power BI

Introduction

• Webinar organized as part of the Microsoft Business Applications Platform Summit.
• Chuck introduces Phil Seamark, a new MVP, to discuss DAX and present a session based on his book.

Phil Seamark's Presentation

• Phil shared his journey of writing a book on DAX, available on Amazon and other platforms.
• The session discusses advanced DAX topics, some of which are covered in his book.
• Attendees can download the PBIX file used in the session.

Key Topics Discussed

Using DAX in Power BI

• Objective: Generate a fake data model for a sales company using DAX, without importing external data.
• Tools Mentioned: DAX Studio and SQL Server Management Studio for optimizing DAX performance.

Creating Tables in Power BI with DAX

• Numbers Table: Created using GENERATESERIES function for numbers 1 to 100.
• Date Table: Created using variables and the CALENDAR function.
  • Introduced use of VAR keyword for variable scope.
  • Added columns for year, month, and day using ADDCOLUMNS.

Generating Sales Data

• Sales Table: Combined numbers and dates tables using GENERATE function.
• Randomized Sales Data: Used RANDBETWEEN function to create random sales entries.
• Product Codes: Generated using nested variables and ASCII codes.
• Demonstrated using SELECTCOLUMNS to filter necessary columns from a table.

Calculating with DAX

• Daily Summary Table: Summarized sales data to daily level using SUMMARIZECOLUMNS.
• Calculated Columns vs. Measures
  • Explained the difference in execution time and data footprint impact.
  • Demonstrated with a cumulative revenue example.

Tools and Optimization

DAX Studio

• Connects directly to Power BI Desktop for testing and optimizing DAX queries.
• Server Timings: Used to analyze query performance (Storage Engine vs. Formula Engine).

SQL Server Management Studio

• Profiler used to capture detailed execution plans and server timings.

Tips for DAX Optimization

• Prefer calculated columns for static data; use measures for dynamic, filter-responsive calculations.
• Utilize DAX Studio and SQL Server Profiler for performance analysis and optimization.

Conclusion

• Phil Seamark encourages using DAX Studio and shares additional resources and practices for improving DAX skills.
• Plans to post the session notes and calculations for further study.

Additional Topics to Explore (from Phil's Notes)

• Tips on ranking, grouping, and pivoting data using DAX.
• Advanced techniques for join operations and data manipulation in DAX.