What is the main difference between M and DAX? Why we can do a calculated column in two different places? What are pros and cons of each? Which one should I use for creating a profit column? Why I cannot do all of it in only one; DAX or M! Why two different languages?! Why structure of these two are so different? … If any of these are your questions, then you need to read this post. In this post I’ll go through differences of these two languages, and explain why, when, where of it. Normally I don’t get this question asked from students of my Power BI course, because I elaborate the difference in details. However, if you have this question, this is a post for you. If you would like to learn more about Power BI; read Power BI book; from Rookie to Rook Star.

What is M?

M is the scripting language behind the scene for Power Query. M is the informal name of this language. the formal name is: Power Query Formula Language! Which is really long, and even Microsoft refer it to M. M stands for many things, but one of the most common words of it is Mashup. Which means this language is capable of data mashup, and transformation. M is a functional language. and structure of M script can be similar to this:

Source: Day Number of Year Function in Power Query

M is a step by step language structure. Usually (Not always), every line in M script is a data transformation step. and the step after that will use the result of previous step. It is usually easy to follow the structure of M language for a programmer. because it is understandable with programming blocks of Let and In, and some other programming language features alike.

What is DAX?

DAX is Data Analysis eXpression Language. This is the common language between SQL Server Analysis Services Tabular, Power BI, and Power Pivot in Excel. DAX is an expression language, and unlike M, it is very similar to Excel functions. In fact, DAX has many common functions with Excel. However DAX is much more powerful than Excel formula in many ways. Here is an example DAX expression:

Source: Secret of Time Intelligence Functions in Power BI

DAX calculations are built in a way that makes sense mostly for Excel users. Normally Excel users are very comfortable with this language. Everything goes through functions. DAX doesn’t have programming blocks in it, and is combination of function uses, filters, and expressions.

Example Usage of M

M can be used in many data transformation scenarios. As an example, it can be used to Pivot or Unpivot Data, To Group it based on a number of columns. Here is how a Pivot/Unpivot can work in Power Query;

Example Usage of DAX?

DAX can be used for many calculation for analyzing data. For example, calculating Year To Date, Calculating Rolling 12 Months Average, or anything like that. Here is an example which based on a selection criteria in the report and few simple DAX expressions we can do a customer retention case with DAX;

Calculated Column Dilemma

The main question of choosing between DAX and M comes from calculated column dilemma in my opinion. You can create many calculated columns in both M or DAX, and it is confusing where is the best place to do it, or why there are two different places to do it?! As an example; you can create a full name which is concatenated of FirstName and LastName column. You can do that in M, and also in DAX. So this question comes up that: Why two different places? Which one is best to use? can we always use one language?

To answer this question, I would like to use another example; There are many types of knives, and you can use almost all of them to cut cheese!

reference: http://forkitchen.blogspot.co.nz/2008/10/what-are-different-types-of-kitchen.html

Almost every knife in above picture can be used for cutting cheese except one of them! So why there are so many knives for cutting cheese?! The answer is that; these are not knives for cutting cheese! each knife is good for doing one special case. for cutting bread, bread knife gives you the best result. for cutting a fillet you normally need another type of knife. but as you agree, for some cases (such as cutting cheese!) you can use many of these knifes. Let’s know go back to the original question;

Why I can create same calculated column in DAX or M?

These two languages built independently. They built in a way that they can handle most of business related solutions. So as a result there are some use cases that both languages are capable of doing it. As an example both of these languages can easily be used to create a concatenated column of two other columns.

Which one is best?

Quick answer is Depends! Depends on type of usage. If you want to create a concatenated column; Power Query (M) is better option in my view, because that is normally like the ETL part of your BI solution, you can simply build your model and data sets in a way you like it to be. But if you want to create something like Year To Date; Obviously you can do that in Power Query or M, but it will be lots of code, and you have to consider many combinations of possibilities to create a correct result, while in DAX you can simply create that with usage of TotalYTD function. So the answer is; there is no best language between these two. The type of usage identifies which one is best. Normally any changes to prepare the data for the model is best to be done in M, and any analysis calculation on top of the model is best to be done in DAX.

Two Languages for Two different Purposes

There are many programming languages in the world, each language has its own pros and cons. JavaScript is a language of web scripting, which is very different from ASP.NET or PHP. Same thing happens here. When M born, it meant to be a language for data transformation, and it is still that language. DAX was created to answer business analysis questions.

What Questions DAX Can Answer?

DAX is the analytical engine in Power BI. It is best language to answer analytical questions which their responses will be different based on the selection criteria in the report. For example; You might want to calculate Rolling 12 Months Average of Sales. It is really hard if you want to calculated that in M, because you have to consider all different types of possibilities; Rolling 12 months for each product, for every customer, for every combinations and etc. However if you use a DAX calculation for it, the analytical engine of DAX take care of all different combinations selected through Filter Context in the report.

What Questions M Can Answer?

M is Data Transformation engine in Power BI. You can use M for doing any data preparation and data transformation before loading that into your model. Instead of bringing three tables of DimProduct, DimProductSubcategory, and DimProductCategory, you can merge them all together in Power Query, and create a single DimProduct including all columns from these tables, and load that into the model. Loading all of these into the model and using DAX to relate these with each other means consuming extra memory for something that is not required to be in the model. M can simply combine those three tables with each other and based on “Step Based” operational structure of M, they can be simply used to create a final data set.

As a Power BI Developer Which Language Is Important to Learn?

Both! With no hesitation!  M is your ETL language, and DAX is the analytical language. You cannot live with only one. If you want to be an expert in Power BI, you should be an expert in both of these languages. There are some cases, that one of the languages will be used more than the other one. However, you will need very good understanding of both languages to understand which one is best for which purpose, and easily can use it in real-world scenarios.

Save

Save

Save

Reza Rad

Trainer, Consultant, Mentor

Reza Rad is a Microsoft Regional Director, an Author, Trainer, Speaker and Consultant. He has a BSc in Computer engineering; he has more than 20 years’ experience in data analysis, BI, databases, programming, and development mostly on Microsoft technologies. He is a Microsoft Data Platform MVP for 12 continuous years (from 2011 till now) for his dedication in Microsoft BI. Reza is an active blogger and co-founder of RADACAD. Reza is also co-founder and co-organizer of Difinity conference in New Zealand, Power BI Summit, and Data Insight Summit.
Reza is author of more than 14 books on Microsoft Business Intelligence, most of these books are published under Power BI category. Among these are books such as Power BI DAX Simplified, Pro Power BI Architecture, Power BI from Rookie to Rock Star, Power Query books series, Row-Level Security in Power BI and etc.
He is an International Speaker in Microsoft Ignite, Microsoft Business Applications Summit, Data Insight Summit, PASS Summit, SQL Saturday and SQL user groups. And He is a Microsoft Certified Trainer.
Reza’s passion is to help you find the best data solution, he is Data enthusiast.
His articles on different aspects of technologies, especially on MS BI, can be found on his blog: https://radacad.com/blog.