Power Bi 简明教程

DAX Basics in Power BI

在本章中，你将学习如何在 Power BI 中使用各种 DAX 函数。

In this chapter, you will learn how to use various DAX functions in Power BI.

DAX Introduction

DAX（数据分析表达式）是一种公式表达式语言，可以用在不同的 BI 和可视化工具中。DAX 也被称为函数语言，其中完整代码保留在函数中。DAX 编程公式包含两种数据类型：数字型和其他类型。数字型包括整数、货币和小数，而其他类型包括字符串和二进制对象。

DAX (Data Analysis Expressions) is a formula expression language and can be used in different BI and visualization tools. DAX is also known as function language, where the full code is kept inside a function. DAX programming formula contains two data types: Numeric and Other. Numeric includes - integers, currency and decimals, while Other includes: string and binary object.

以下是 DAX 公式的一个示例，它使用一个函数来计算表中的一列。

Following is an example of DAX formula, which uses a function to calculate a column in a table.

dax formula

DAX 函数还可以包括其他函数、条件语句和值引用。

DAX function can also include other functions, conditional statements, and value references.

DAX Functions

在 Power BI 中，你可以使用不同类型的函数来分析数据，并创建新的列和度量。它包括来自不同类别的函数，例如 -

In Power BI, you can use different function types to analyze data, and create new columns and measures. It includes functions from different categories such as −

  1. Aggregate

  2. Text

  3. Date

  4. Logical

  5. Counting

  6. Information

Power BI 提供了一种查看所有函数列表的简单方法。当你开始在公式栏中输入函数时，你可以看到从该字母开头的所有函数的列表。

Power BI provides an easy way to see the list of all functions. When you start typing your function in the formula bar, you can see the list of all functions starting with that alphabet.

all functions

Aggregate Functions

DAX 具有许多聚合函数。

DAX has a number of aggregate functions.

  1. MIN

  2. MAX

  3. Average

  4. SUM

  5. SUMX

Counting Functions

DAX 中的其他计数函数包括 −

Other counting functions in DAX include −

  1. DISTINCTCOUNT

  2. COUNT

  3. COUNTA

  4. COUNTROWS

  5. COUNTBLANK

Logical Functions

以下是对逻辑函数的收集 −

Following are the collection of Logical functions −

  1. AND

  2. OR

  3. NOT

  4. IF

  5. IFERROR

TEXT Functions

  1. REPLACE

  2. SEARCH

  3. UPPER

  4. FIXED

  5. CONCATENATE

DATE Functions

  1. DATE

  2. HOUR

  3. WEEKDAY

  4. NOW

  5. EOMONTH

INFORMATION Functions

  1. ISBLANK

  2. ISNUMBER

  3. ISTEXT

  4. ISNONTEXT

  5. ISERROR

DAX Calculation Types

在 Power BI 中，可以使用 DAX 创建两种主要计算 −

In Power BI, you can create two primary calculations using DAX −

  1. Calculated columns

  2. Calculated measures

导航到“建模”选项卡时，可以在屏幕顶部看到“新建列”选项。它还将打开可在此处输入 DAX 公式以执行计算的公式栏。DAX - 数据分析表达式在 Excel 中是一种用于执行计算的强大语言。还可以通过更改公式栏中的“列”文本来重新命名此列。

When you navigate to the Modeling tab, you can see a New Column option at the top of the screen. This also opens the formula bar where you can enter DAX formula to perform the calculation. DAX - Data Analysis Expression is a powerful language used in Excel to perform calculations. You can also rename the column by changing the Column text in the formula bar.

excel to perform calculations

在以下示例中，我们创建了一个新列：产品编码 (Product_C)，它派生自 Prod_Id 列的最后 3 个字符。以下是该公式 −

In the following example, we have created a new column: Product Code (Product_C), which is derived from the last 3 characters of Prod_Id column. Following is the formula −

Product_C = RIGHT( Sheet1[Prod_Id],3)

若要创建计算度量值，请导航到“建模”下方的“新建度量值”选项卡。这将在“字段”选项卡下添加一个新对象，并将其命名为度量值。

To create a calculated measure, navigate to New Measure tab under Modeling. This will add a new object under the Fields tab with the name Measure.

create calculated measure
new measure tab

可以编写 DAX 公式来计算新度量值的值，就像我们对新计算列所做的那样。

You can write DAX formula to calculate the value of the new measure, as we did for the new calculated column.