power bi dax

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

Microsoft Power BI支持两种不同的语言 即M语言和DAX 数据分析表达式 可用于过滤 管理和可视化数据。

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.

M可以视为查询公式语言 可以在Power BI查询编辑器中使用M 以便在将数据加载到Power BI模型之前准备数据。

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.

另一方面 DAX是一种分析数据计算语言 可用于“数据视图”阶段的深入数据分析。

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.

M和DAX不相互依赖 遵循完全不同的结构和逻辑 并且具有不同的基础代码。 M和DAX无法同时使用 因为在查询编辑器中使用M语言 而在数据视图模型中则主要使用DAX。

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.

这篇博客文章将通过示例帮助您了解M和DAX在Power BI中的区别。 请参阅本文以快速了解DAX 。

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.

在本节中 我们将导入一个虚拟数据集 该数据集将用于执行M语言和DAX查询。 为此 请单击顶部菜单中的“获取数据”按钮 如以下屏幕截图所示。

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

一个新的对话框将列出打开到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.

在搜索部分插入“ Web” 不久将出现“ Web”数据选项 并且在“ 所有数据源”选项中也会显示一个图标。 选择“ Web”选项 然后单击底部的“连接”。 这在下面的屏幕截图中很明显。

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:

您将在下面的屏幕快照中看到的对话窗口将会出现。 现在 您需要将下面的URL插入标有URL的字段中

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

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:

这是屏幕截图供您参考

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.

此URL将Power BI带到Github Github包含我们需要的CSV数据文件。 数据文件详细描述了电信客户流失数据集。 它包含有关电信公司客户的数据 该数据在一段时间后离开了该公司。

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:

数据下载完成后 您有两个选择 要么直接将数据导入到“数据/报表视图” 要么首先将数据加载到“查询编辑器”中。 由于M语言可在查询编辑器中使用 因此我们将首先通过单击“转换数据”将数据加载到查询编辑器中 如以下屏幕截图所示

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.

单击“转换”按钮后 应该会看到以下查询编辑器。

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.

在上一节中 我们将Power BI连接到GitHub上的虚拟数据集。 在本节中 您将借助示例了解M语言和DAX之间的区别。

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.

首先 我们将了解M是什么以及如何在Power BI中使用它。 如前所述 M语言用于在查询编辑器中管理和过滤数据。 如果您关闭了查询编辑器 则可以通过单击顶部菜单中的“编辑查询”选项来将其打开。

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:

在查询编辑器中 屏幕左侧面板上唯一的表名称将是您的表 如下所示。 从查询编辑器的左侧面板中单击“ WA_Fn-UseC_-Telco-Customer-Churn”数据表 如下所示

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:

要查看M中的查询 请选择任何列 例如“ Partner” 然后从顶部菜单中单击“ Remove Columns”按钮。 它将从表中删除“合作伙伴”列 如下所示

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

接下来 单击顶部菜单中的“查看”选项 如以下屏幕快照中突出显示的那样。

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#.

单击“配方栏”复选框。 现在 您应该在编辑栏中看到一个查询 如以下屏幕快照中突出显示的那样。 这里的查询是“ Table.RemoveColumns “更改类型” {“合作伙伴”} 。 此查询是一种M语言查询 用于从所选表中删除“合作伙伴”列。 您可以看到 M语言查询与C 中SQL或LINQ 语言集成查询 命令更为相似。

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

要查看数据集中所有先前执行的M查询 可以单击“视图–高级编辑器” 如以下屏幕截图所示。

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.

单击顶部菜单中的“高级编辑器”选项后 您将看到如下所示的“高级编辑器”窗口。 “高级编辑器”窗口包含您在当前会话中迄今为止执行的所有M语言查询。

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:

让我们尝试在books表中添加一个简单的列 并查看M查询。 在窗口顶部的菜单中 点击“添加列- 自定义列”选项 如下所示

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.

您应该看到下面的窗口。 依次单击“总计费用”和“每月费用”列 然后单击“插入”按钮以将它们插入查询编辑器窗口中。 在查询编辑器窗口中 在两个列的名称之间添加“ ”号 然后单击“确定”按钮。

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.

您应该看到一个新列“ Monthly-Total”添加到表中 如下所示。 在公式栏中 您还可以看到生成的M查询以创建“月总计”列。 “每月总计”列基本上在“总计费用”和“每月费用”列中显示值的总和。

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.

现在 如果再次打开“高级编辑器”窗口 您将在高级编辑器中查询的底部看到用于添加“每月总数”列的查询。

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).

现在我们知道了M语言的工作原理和外观。 在下一节中 我们将尝试通过DAX 数据分析表达式 创建相同的“每月总计2”列。

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:

DAX查询在数据视图中执行 因此 我们必须将数据从查询编辑器移动到数据视图。 为此 在查询编辑器中 单击顶部菜单中的“关闭并应用”选项 如下所示

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

单击“关闭并应用”按钮后 将打开数据视图。

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:

在本节中 我们将看到如何使用DAX将新列添加到Books表中。 在数据视图中 从顶部菜单中单击“建模- 新建列”选项 如下所示

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.

单击“新建列”选项后 应该会看到以下窗口。 在编辑栏中 您可以看到默认的列名称。 您可以在此编辑栏中执行DAX查询。

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.

在编辑栏中输入查询“每月总计2 [每月费用] [每月费用]” 然后按“ Enter”键。 按下Enter键后 您将看到一个新列“ Monthly-Total 2” 该列在“ TotalCharges”和“ MonthlyCharges”列中显示值的总和。

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

您可以看到DAX查询与MS Excel更相似。

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.

M语言和DAX是Power BI支持的两种用于处理 过滤和分析数据的语言。 尽管两种语言具有相似的功能 但它们彼此独立地运行。 M用于在查询编辑器中预处理数据 而DAX用于在将数据加载到数据视图模型中之后分析数据。

翻译自: https://www.sqlshack.com/differences-between-the-m-language-and-dax-in-power-bi/

power bi dax