Power BI – Differences between the M Language and DAX

Power BI supports both M Language and DAX as expression languages. Both are more comparable to the formulas in Microsoft Excel than they are to any programming language. However, M and DAX are distinct from one another and are applied in various ways when creating Power BI models. As we get to know Microsoft’s new Power BI, we understand there’s more going on behind the scenes. Besides the fact that it considers straightforward and simple admittance to every one of the information sources in your association, it additionally empowers you to control the information in manners that were unthinkable previously.

Both DAX and M languages are included in the most recent edition of Power BI. By applying computations to the incoming data or by connecting to additional data sources and running queries against them, both are utilized to alter the data. You will learn all there is to know about DAX and M language in this post, including how they interact and how they can increase the value of your data. The new Power BI programming language M combines the readability of Excel with the flexibility of SQL. It is made to be easy to understand, read, and utilize.

M Language

M Query is a “mashup” query language that can be used to query a lot of data from many sources.
It is used in the first part of the data import process for Power BI Desktop, which is when data is loaded into the data model and queries are run in the background using M.

M Language in Power BI

When importing data, M query is frequently used to query data sources, clean and load the data, and build custom columns.

Dataset

A dataset is a group of data that you connect to or import. Power BI enables you to connect to, import, and combine many datasets in one location. Additionally, dataflows can provide data to datasets. workspaces and datasets are related, and a single dataset might be a component of numerous workspaces. In this we have used the Employee Dataset, this dataset contains columns such as DeptID, Emp Name, Salary, Bonus, DOJ, Gender, and City. and this dataset contains 35 rows. The source of this dataset is taken from the Excel workbook. Look at the picture below to know about the dataset used.

How to Add a Custom Column using M Language?

Open Power BI desktop >> Get Data >> Choose any data sources (Ex: Excel workbook) >> choose required file >> select required tables >> load. Then

Step 1: Select a custom column from the query editor. Go to Add new column >> select custom column as shown in the picture.

Step 2: Give your new column a name. Here we wanted to create a new Increment column on the employee table.

Step 3: Create a formula for the column. For this, we selected the “salary” column from the available columns option, because we wanted to add an Increment column on the base of the salary of the employee. After selecting the required column from the available columns option select ” Insert “. then go to the custom column formula box and try to write the formula (Ex: [salary] + 2000).

Step 4: Check that there are no syntax errors in your formula then press OK.

Now we created a new column using M language as shown in the picture.

The M code for the full query can be viewed and edited in the advanced editor, as opposed to the formula bar, which only displays the M code for the step in the query that is now being selected. There are two locations in the editor ribbon where you can access the advanced editor. Press the Advanced Editor button from the Home tab or the View tab.

Programming Language M is Multi-Paradigm

• Declarative: Let the state what needs to be done rather than how it has to be done. You can use code to describe your intentions more.
• Practical: All capabilities have a similar language structure (paying little heed to where they are executed), which simplifies it for you to fathom them some other time when they are refactored or migrated across your model. You can test your code all the more effectively and just thus.
• Object-Situated: Offers a class framework that empowers you to bundle state and conduct in objects that you can reuse across your model.

To view the M-formatted queries. Choose any table, choose any column, such as “DOJ,” and then select “Remove Columns” from the top menu. The “DOJ” column in the table will be eliminated. 

Next click on the “View” option as shown in the picture. Click on the “formula bar” check box. The formula bar now displays a query, as shown in the picture below.

The query here is == Table.RemoveColumns(#”Changed Type”,{“DOJ”}). This query is an M language query.

DAX (Data Analysis Expression)

In Power BI, computations are expressed using the DAX formula language. It is based on Microsoft Excel formulas, but unlike the latter, it is not exportable to other programmers and can only be utilized within Power BI. Based on the available data source, the DAX syntax enables you to define measurements, calculated columns, and fields.

Columns with Measures and Calculations

In the syntax, formulae that return values are enclosed in brackets (). In order for Power BI to properly understand logic or nested constructs like IF statements, THEN/ELSE IF/ELSE constructs, DO loops, or WHILE loop constructions when evaluating your expressions, you must enclose them in parenthesis. In Power BI, measurements and calculated columns are also created using DAX. We’ll demonstrate how to use DAX to add a new column to the Employee table. Select “Modeling -> New Column” from the Data View menu.

“New Column” menu item. The name of the default column is displayed in the formula bar. The DAX queries can be run in this formula bar. shown in the below picture.

Example for DAX

Differences between DAX and M Language

A formula language is DAX. A query language is called M Language. M Language is less potent than DAX. For instance, M cannot currently define KPIs and measurements, but DAX can. Both of these languages have some differences. Nevertheless, they have extremely similar functions, as follows: Using straightforward syntaxes, they enable you to do data queries within the Power BI service and then display the results using graphs and charts. The biggest difference between them is how versatile they are when building calculated fields or calculated measures to be used in reports; at this time, DAX allows for far greater sophistication than M. (though this will likely change over time). Since its initial release in 2010 with Power Pivot for Excel, the M language has been integrated into the Power BI service.

Although the M language and DAX are quite similar, there are certain distinctions that limit what you can do with it. For instance, while you can define calculated columns and do computations on measures in both languages, DAX measures can be much more complicated than M measures.

M and Dax’s Simple and Elegant Approach

The world of Power BI is becoming simpler and more elegant thanks to M language and DAX. Due to their comparative syntaxes, you may easily learn one language and use it in a variety of contexts. M has been around for longer than DAX, but DAX is becoming more and more popular as a result of its robust functionality and ease of use. Because it doesn’t require the user to be familiar with all the technical variables, DAX is a more widely used language than M and is simpler to learn. Although DAX can handle the majority of these calculations with just one line of code, M can still conduct sophisticated computations, which is why some businesses continue to use it.

Conclusion

M comes in second place to DAX in Power BI. With a focus on making, it simpler for data scientists and business users to construct models and gain insights into their own situations, M delivers simplicity and elegance to the world of Power BI. As a result of its greater flexibility than M, DAX has been used by numerous companies across numerous industries.

In light of this, depending on the goals, either approach will work well if you’re considering building models in Power BI today. With the help of this post, I hope you now have a better knowledge of the distinctions between DAX and M and how you can utilize them to construct reliable data models in Power BI.