The Clearly Podcast

Microsoft Power BI M vs DAX

Summary

This week we venture into the world of hairdressing - and the 2 programming languages at the core of Power BI.

In a nutshell, M is the language used for importing data into Power BI and DAX the language used to manipulate data inside the Power BI data model.

Reza Rad  deals with the differences in his blog post M or DAX? That is the question.

Chris Webb posts a huge amount of really good material on his blog at Cross Join.

DAX Patterns is one of the richest resources for writing DAX measures, by the authors of The Definitive Guide to DAX (also on Amazon US).

Once you are starting to get beyond the basics in either language, the external tools such as DAX Studio and Visual Studio Code offer much better development environments for developing your code.

The full documentation on DAX can be found here and the documentation for M is here.

Check out Luke McAdam's Instagram and Soundcloud for more of his music.

If you are only here for the haircare products, you can also order DAX Short and Neat online.

You can download Power BI Desktop from here.

If you already use Power BI, or are considering it, we strongly recommend you join your local Power BI user group here.

Transcript

Host: Andy Clark

  • Welcome to the Clearly Podcast, Episode 8, "M versus DAX," recorded on November 27, 2020.

  • Featuring Shailan Chudasama, Tom Gough, and myself.

  • Discussion focuses on the differences between Power Query’s M language and Power BI’s DAX language.

  • Covers best practices and differentiates between DAX the data modeling language and Dax the hair care product (not a sponsor).

Intro Chat

  • General banter about the weather in Houston and Wales.

  • Acknowledgment of Luke McAdam for the podcast’s intro music.

Discussion on M and DAX

Tom: Explanation of M

  • M is the Power Query Formula Language used for data loading and transformation in Power BI and Excel.

  • Processes data table by table, row by row.

  • Great for reducing data sets and pre-processing before data reaches Power BI.

Shailan: Explanation of DAX

  • DAX stands for Data Analysis Expressions, used within Power BI for adding measures, calculations, and columns.

  • Similar to Excel formulae, allowing filtering and calculating within the data model.

  • Offers a wide range of functions for complex calculations.

Andy: Comparing M and DAX

  • M is used for ETL (Extract, Transform, Load) processes to clean up data before it reaches Power BI.

  • DAX is used within Power BI for data calculations and aggregations.

  • Both can be used to create columns and perform calculations, but their use cases and efficiencies differ.

Tom: Choosing Between M and DAX

  • Both languages have overlapping functionalities, but they serve different purposes.

  • M is more table-specific while DAX can perform more holistic data model calculations.

  • Test both scenarios to determine the best performance for specific tasks.

Shailan: Performance and User Access

  • M transformations are done at the query level, while DAX calculations are often performed within the data model.

  • Users editing reports can see DAX formulas but not M transformations, providing different levels of data access and security.

Tom: Performance and Maintenance

  • Calculations in DAX vs. M have minimal performance differences but vary with direct queries.

  • Avoid unnecessary M transformations on direct queries to maintain performance.

  • Documenting calculations and transformations is crucial for future maintenance and clarity.

Shailan: Resources for Learning M and DAX

  • Microsoft resources, Clearly Cloudy blog, and community blogs like Reza Rad’s and DAX Patterns are valuable for learning.

  • Communities and forums are helpful for troubleshooting and gaining insights.

Top Tips

  • Tom: Test both M and DAX to see which offers better performance.

  • Shailan: Isolate models for testing, use resources, and document your work.

  • Andy: Focus on delivering fast, efficient reports for a better user experience.

  • Tom: Ensure maintainability; don’t sacrifice clarity for performance.

Closing Remarks

  • Fix data issues at the source rather than using M or DAX to hide errors.

  • Utilize Clearly Cloudy’s health check service for auditing and improving Power BI reports.

  • Document your work to help others and yourself in the future.

Outro

  • Thank you for listening. Next episode on December 21 will cover data visualization pitfalls.

  • Send questions or topic suggestions to podcast@clearlysolutions.net.