Ben Richardson
Executing a DAX query in the formula bar

Differences between the M Language and DAX in Power BI

May 14, 2020 by

Microsoft Power BI supports two different languages, M language and DAX (Data Analysis Expression) that can be used to filter, manage, and visualize data.

M can be considered as a query formula language and it can be used in the Power BI Query Editor in order to prepare data before it can be loaded into the Power BI model.

On the flip side, DAX is an analytical data calculation language which can be used for in-depth data analysis during the Data View phase.

M and DAX are not dependent upon each other and follow totally different structures and logics, and have different underlying codes. M and DAX cannot be used simultaneously since the M language is used in Query Editor while DAX is mostly used in the Data View model.

This blog post will show you how M and DAX differ from each other in Power BI, with the help of examples. See this article for a quick introduction to DAX.

Importing the Dataset

In this section, we will import a dummy dataset that we will use to execute our M Language and DAX queries. To do this start by clicking the “Get data” button in the top menu as shown in the following screenshot.

Selecting the Get Data choice in Power BI

A new dialogue will list all the data links open to Power BI.

Insert web” into the search section, and very shortly a Web” data option will appear and also an icon will show in the All data source options. Select the Web” option and then click “connect” at the bottom. This is clear in the screenshot below.

Selecting the 'Web' data source.

The dialogue window you can see in the screenshot below will appear. You now need to insert the URL below into the field labelled URL:

https://raw.githubusercontent.com/treselle-systems/customer_churn_analysis/master/WA_Fn-UseC_-Telco-Customer-Churn.csv

Click “OK” to confirm when this is done.

Here is a screenshot for your reference:

Entering the URL for the data source that we want to use.

This URL takes Power BI to Github which holds the CSV data file that we need. The data file details a telecom customer churn dataset. It contains data about the customers of a telecom company, that left the company after a certain period.

Depending upon the size of the file, it might take a while to download the data from the remote site.

Once the data is downloaded, you have two options: either you can directly import the data to Data/Report View, or you can first load the data into Query Editor. Since the M Language works in the Query Editor, we will first load the data into the Query Editor by clicking “Transform Data” as shown in the following screenshot:

Our data set showing in Query Editor.

You will see that the dataset contains information about a customer, such as a gender, tenure, phone service, internet service, dependents etc.

Once you click the Transform button, you should see the following Query Editor.

Our dataset once we've clicked 'Transform'

Differences between M Language and DAX

In the previous section, we connected Power BI to a dummy dataset on GitHub. In this section, you will see with the help of examples, the differences between M Language and DAX.

M Language

First, we will see what M is and how it is used in Power BI. As I said earlier, the M Language is used to manage and filter data in the Query Editor. If you have closed the Query Editor, you can open it by clicking the “Edit Queries” option from the top menu.

Reopening the query editor by clicking edit queries

In the Query editor, the only table name on the left panel of the screen will be your table, as shown below. Click on the “WA_Fn-UseC_-Telco-Customer-Churn” data table from the left panel of the query editor as shown below:

Screenshot showing what happens when the table name is clicked.

To see the queries in M. Select any column, for example, “Partner” and then click the “Remove Columns” button from the top menu. It will remove the “Partner” column from the table as shown below:

Removing the partners column from our data

Next, click on the “View” option from the top menu as highlighted in the following screenshot.

Selecting View from the top menu

Click the “Formula Bar” checkbox. You should now see a query in the formula bar as highlighted in the following screenshot. The query here is “= Table.RemoveColumns(#”Changed Type”,{“Partner”}). This query is an M language query used to remove the “Partner” column from the selected table. You can see that the M Language query is more similar to an SQL or LINQ (Language Integrated Query) command in C#.

Seeing the query contained in the 'Dependents' column in Power BI

To see all the previously executed M queries in your dataset, you can click on “View – Advanced Editor” as shown in the following screenshot.

Using 'Advanced Editor' in Query Editor to show all M language queries.

Once you click on the “Advanced Editor” option from the top menu, you should see the Advanced Editor window as shown below. The Advanced Editor window contains all the M language queries that you have executed till now in the current session.

Output of Advanced Editor showing previous M queries.

You can see that the last query is the one that removed the “Partner” column from the table. Before that, the “Change Type” query is used to assign the data types to different columns in the dataset. And previous to that, the “Promoted Headers” query is used to set the header columns. If you execute another query, that will appear at the bottom in the query list in the “Advance Editor”.

Let us try to add a simple column in the books table and see the M query for that. Click on the “Add Column -> Custom Column” option in the menu at the top of the window, as you can see below:

Adding a custom column.

You should see the window below. Click on the “TotalCharges” and “MonthlyCharges” columns one by one and then click the “Insert” button to insert them on the query editor window. In the query editor window, add a “+” sign between the two-column names and click the “OK” button.

Seeing up a custom column.

You should see a new column “Monthly-Total” added to the table as shown below. In the formula bar, you can also see the M query generated to create the “Monthly-Total” column. The “Monthly-Total” column basically displays the sum of values in the “TotalCharges” and “MonthlyCharges” columns.

The new column showing in Query Editor.

Now if you again open the “Advanced Editor” window, you will see the query used to add the “Monthly-Total” column at the bottom of the queries in the advanced editor.

The Advanced Editor showing our new query.

Now we know how the M Language works and how it looks like. In the next section, we will try to create the same “Monthly-Total 2” column via DAX (Data Analysis Expression).

DAX queries are executed in the Data View; therefore, we have to move the data from the Query Editor to the data view. To do so, in the Query Editor, click on the “Close & Apply” option from the top menu as shown below:

Opening the Data view in Power BI.

Once you click the “Close & Apply” button, the Data View will be opened.

DAX (Data Analysis Expression)

In this section, we will see how to add a new column to the Books table using DAX. In the Data View, click on “Modeling -> New Column” option from the top menu as shown below:

Creating a new column in the data view.

You should see the following window once you click the “New Column” option. In the formula bar, you can see the default column name. You can execute the DAX queries in this formula bar.

Executing a DAX query in the formula bar

Enter the query “Monthly-Total 2 = [TotalCharges] + [MonthlyCharges]” in the formula bar and hit the “Enter” key. Once you hit enter, you will see a new column “Monthly-Total 2” which displays the sum of values in the “TotalCharges” and “MonthlyCharges” columns.

Our new formula operating in the formula bar.

You can see that the DAX queries are more similar to MS Excel.

Conclusion

M Language and DAX are the two languages supported by Power BI to manipulate, filter and analyse the data. Though both languages have similar functionalities, they operate independently of each other. M is used to pre-process the data inside the Query Editor whereas DAX is used to analyse the data after the data is loaded into the Data View Model.

Ben Richardson
256 Views