Enhancing Data Analysis with DAX | DAX Tutorial

  In this session from Pragmatic Works’ 2025 Intergalactic Learn with the Nerds Day, Mitchell Pearson dives deep into the power of DAX (Data Analysis Expressions) within Power BI. Moving beyond traditional sales data, Mitchell explores project-based datasets to demonstrate how DAX can be used to build insightful and dynamic reports.

Key Topics Covered

• Understanding filter context in DAX
• Building foundational DAX measures
• Time intelligence functions
• Using Copilot to generate DAX
• Organizing and debugging DAX measures

Building the Data Model

Mitchell emphasizes the importance of a well-structured data model. The session uses a project task fact table surrounded by dimension tables (projects, employees, date, and location). He stresses that a solid model is essential for effective DAX calculations and filter propagation.

Creating Basic Measures

1. Project Hours: A simple SUM of the number of hours from the project task table.
2. Total Projects: Uses DISTINCTCOUNT on project IDs within the project task table to ensure filter context is respected.
3. Total Project Tasks: Uses COUNTROWS to count tasks, assuming one row per task.

Filter Context Explained

Mitchell defines filter context as the filters applied through active relationships in the data model. He demonstrates how measures respect these filters unless explicitly overridden using functions like CALCULATE.

Enhancing Visualizations

He showcases how to use data labels and the “details” feature in Power BI visuals to add contextual information like total projects directly onto charts, improving readability and insight.

Time Intelligence with DAX

• Year-to-Date (YTD): Uses TOTALYTD to calculate cumulative project hours.
• Prior Year Comparison: Uses SAMEPERIODLASTYEAR to compare current YTD with the previous year.
• Year-over-Year Growth: Calculates percentage growth using DIVIDE and previously created measures.
• Prior Month Analysis: Uses DATEADD to shift the filter context back one month.

Percent of Total

To calculate the percent of total project hours by month, Mitchell demonstrates how to remove filters using REMOVEFILTERS and ALLEXCEPT to isolate the year-level total for accurate division.

Organizing Measures

Inspired by Matt Allington, Mitchell recommends placing all columns into a display folder and leaving measures in the root for easier access. He also shows how to move and rename measures efficiently within the model view.

Debugging with DAX Query View

The new DAX query view in Power BI allows users to trace measure dependencies and evaluate them step-by-step. This is especially useful for debugging complex calculations and understanding measure lineage.

Using Copilot for DAX

Mitchell demonstrates how to use Power BI’s Copilot to generate DAX measures. While Copilot can produce accurate results, he notes that it may require manual adjustments and validation. He also explains the prerequisites for using Copilot, such as having a Fabric capacity workspace.

Conclusion

This session is a comprehensive guide for anyone looking to deepen their understanding of DAX in Power BI. From foundational concepts to advanced time intelligence and Copilot integration, Mitchell Pearson provides practical insights and real-world applications that empower users to build smarter, more dynamic reports.

Don't forget to check out the Pragmatic Works' on-demand learning platform for more insightful content and training sessions on Dax and other Microsoft applications. Be sure to subscribe to the Pragmatic Works YouTube channel to stay up-to-date on the latest tips and tricks.