The official PASS Blog is where you’ll find the latest blog posts from PASS community members and the PASS Board. Contributors share their thoughts and discuss a wide variety of topics spanning PASS and the data community.

M and DAX in Power BI, When to Use Which Language?

If you are planning on working with Power BI, then there are at least two data manipulation languages you will need to learn: M and DAX. The problem is that it isn’t always clear which one you should be using. At times, these can seem a bit redundant – why do we have two languages?

Historically, M and DAX came from two different Excel add-ins: Power Query and Power Pivot. Originally, they served two different purposes and were only loosely connected. Later on, they were combined together with HTML5-based visual rendering to create what we know as Power BI. This means that these tools are going to have some overlap in functionality.

Let’s walk through three different factors in deciding which language to use.

Ease of Use

If you are just starting to learn Power BI, I recommend working with M whenever possible. When working with M, the Power Query Editor allows you to do most of the work via the GUI without ever writing any code. On top of that, M is much easier to validate because the Power Query Editor allows you to see what the data looks at each step of the way. Finally, M code works in a linear fashion, similar to a cooking recipe. This makes M much more intuitive.

DAX on the other hand is much more complicated but also quite powerful. With DAX, you will be writing code very similar to Excel formulas. However, while it looks similar to Excel, you will need to learn a number of new concepts to succeed. In DAX, you need to be able to think in terms of columns and tables instead of rows and cells.

Cleansing Versus Modelling

Often, our data is not in quite the right shape. We may need to rename columns, remove rows, unpivot tables and so on. These data cleansing and shaping tasks are often tedious and simple. This is what the M language excels at. This is basic data prep, or what some people call “data wrangling” because it’s such an informal process.

M excels at data cleansing. It has support for a large number of simple tasks that are common across all industries and types of data models. M is ideal for the basic work necessary to get the data into a manageable shape, the work you’d have to get done no matter what.

Some work, however, is very specific to the business. Sometimes it’s not just a matter removing columns, but defining how we measure the business and overall success. The specifics of these measures can vary from business to business. For example, one business might have internal customers that should be excluded from gross sales calculations. Another business might want to count certain internal projects when measuring technician utilization. These little details vary from business to business.

DAX is ideal for the work where we take the unique shape of the business and put it into measures. DAX is not great about modifying and cleaning the data, but instead collecting and combining the data in different ways. DAX is where you should be implanting the unique business logic of your organization.

Modification Versus Aggregation

If you need to modify the underlying data in some sort of way, you will likely want to use M. Not only does the Power Query editor allow you to view these changes at each step of the way, but M also has a lot of tools to help you.

DAX, on the other hand, was made to modify data. You can create calculated columns and tables, but often you risk creating duplicate data in a way that’s confusing to the end user.

What DAX was designed for is filtering and aggregation. It was designed for collecting all of the data, leaving some of it out based on logic, and combining it into a single value. This is where DAX shines: in aggregating the data into measures and KPIs for your business.

Eugene Meidinger
About the author

Eugene Meidinger works as a BI consultant and full time Pluralsight author. He currently focuses on content on Power BI and related products. He also leads the Pittsburgh Power BI User Group. He is certified in querying and administering SQL Server.

Please login or register to post comments.

Theme picker

Back to Top
cage-aids
cage-aids
cage-aids
cage-aids