Do You Know the Differences Between M and DAX in Power BI?

Both M and DAX are expression languages for Power BI. Both have more similarity to Microsoft Excel expressions than similarity to any programming language. However, M and DAX are different from each other and are used differently for building Power BI models. M is the query language that can be viewed in the Power Query Editor. This query editor is accessed using the Edit Queries button in Power BI Desktop.

The Power Query Editor offers a myriad of transformations that can be made with menu buttons on the four ribbons: Home, Transform, Add Column, and View.

The expressions in M are automatically generated when transformations are done.  If you enable the Formula Bar on the View ribbon, you will see M expressions in the formula bar which will display for every transformation you make.

You can also click on the Advanced Editor button on either the Home or View ribbons.

Power BI tracks all the transformations that you make in the Applied Steps display as well.

You can do more than just view the M expressions. You can also delete steps from Applied Steps (click the X icon to the left of the step) to roll back a transformation that you have made that doesn’t do what you want to do.

You can also edit the M expression manually. This can be useful if, for example, you want to move a file that you imported, and then you need the file path in M to reference the new path.

On the other hand, DAX is the language you use when you create transformations in the Power BI Desktop main screens rather than via the query editor. For example, if you add a new column and need to do a calculation, you use the DAX expression language. Below is an example of a DAX expression to compute a value for the Sales per capita custom column.

Typically, DAX offers you more capability than you can get from menu transformations in the Power Query Editor. In fact, there are over 200 DAX functions, which you can review here:

https://msdn.microsoft.com/en-us/query-bi/dax/dax-function-reference.

It takes a lot of experience to master DAX, whereas M is generated for you and is editable; you only need to become familiar with the syntax by viewing M code as you build your models.

Want to learn more? Attend the next virtual session of Power BI Essentials hosted by the RSM Technology Academy! Make a profile on our website (academy.rsmus.com) and register today!

By: Mary Ann Hand

RSM Staff