Explaining Lineage in DAX

查看原文 / View Original 编辑 / Edit

Explaining Lineage in DAX

DAX 中的血缘关系（Lineage）详解

One of the most important concepts in DAX is lineage. It’s about the information on where something comes from. Let’s see what it is and how we can manipulate it. DAX 中最重要的概念之一就是“血缘关系”（Lineage）。它指的是关于数据来源的信息。让我们来看看它是什么，以及我们如何对其进行操作。

Introduction

引言

In DAX, lineage is an important concept, and it is vital to understand how to work with and manipulate it. As I did in past articles, I will use DAX queries to explain this concept and its effects. I start with a simple query to get the order count for the product of the brand “Adventure Works”: 在 DAX 中，血缘关系是一个重要的概念，理解如何使用和操作它至关重要。正如我在过去的文章中所做的那样，我将使用 DAX 查询来解释这个概念及其影响。我先从一个简单的查询开始，获取品牌“Adventure Works”的产品订单数量：

EVALUATE 
CALCULATETABLE(
    SUMMARIZECOLUMNS(
        'Date'[Year]
        ,'Date'[MonthShortName]
        ,'Date'[MonthKey]
        ,'Product'[ProductCategoryName]
        ,"Order Count", [Online Order Count]
    )
    ,'Product'[BrandName] = "Adventure Works"
)
ORDER BY 'Date'[MonthKey] ,'Product'[ProductCategoryName]

This is an extract of the result from the query: 以下是该查询结果的摘录：

Figure 1 – Result of the base query (Figure by the Author) 图 1 – 基础查询结果（图片由作者提供）

This query returns 180 rows. Keep it in mind, as it will be important later on. Next, I will introduce a filter for a specific month and show the lineage’s role. 该查询返回 180 行。请记住这一点，因为它在后面很重要。接下来，我将引入一个特定月份的筛选器，并展示血缘关系的作用。

Set the lineage

设置血缘关系

I will add a filter for April 2026: 我将添加一个 2026 年 4 月的筛选器：

DEFINE VAR YearMonthFilter = 202604
EVALUATE 
CALCULATETABLE(
    SUMMARIZECOLUMNS(
        'Date'[Year]
        ,'Date'[MonthShortName]
        ,'Date'[MonthKey]
        ,'Product'[ProductCategoryName]
        ,"Order Count", [Online Order Count]
    )
    ,'Product'[BrandName] = "Adventure Works"
    ,'Date'[MonthKey] = YearMonthFilter
)
ORDER BY 'Date'[MonthKey] ,'Product'[ProductCategoryName]

In this case, I define a variable and set the value to 202604. Next, I add it as a filter to the CALCULATETABLE() function. Nothing special so far. This is the result: 在这种情况下，我定义了一个变量并将值设置为 202604。接下来，我将其作为筛选器添加到 CALCULATETABLE() 函数中。目前为止没什么特别的。结果如下：

Figure 2 – Query and result of the query with a simple filter (Figure by the Author) 图 2 – 使用简单筛选器的查询及其结果（图片由作者提供）

In this case, the lineage is not important, as a scalar value sets the filter. But we can set a lineage by using the TREATAS() function: 在这种情况下，血缘关系并不重要，因为标量值设置了筛选器。但我们可以通过使用 TREATAS() 函数来设置血缘关系：

DEFINE VAR YearMonthFilter = TREATAS({ 202604 }, 'Date'[MonthKey])
EVALUATE 
CALCULATETABLE(
    SUMMARIZECOLUMNS(
        'Date'[Year]
        ,'Date'[MonthShortName]
        ,'Date'[MonthKey]
        ,'Product'[ProductCategoryName]
        ,"Order Count", [Online Order Count]
    )
    ,'Product'[BrandName] = "Adventure Works"
    ,YearMonthFilter
)
ORDER BY 'Date'[MonthKey] ,'Product'[ProductCategoryName]

As you can see, the introduction of TREATAS() allows us to pass the variable as a filter. CALCULATETABLE() uses the lineage set by TREATAS() as a filter on the column ‘Date’[MonthKey]. The result doesn’t change, but the query is simpler, as I don’t need to pass the condition as “column equals the filter-value”. 正如你所见，引入 TREATAS() 允许我们将变量作为筛选器传递。CALCULATETABLE() 使用由 TREATAS() 设置的血缘关系作为 ‘Date’[MonthKey] 列上的筛选器。结果没有改变，但查询变得更简单了，因为我不需要将条件写成“列等于筛选值”的形式。

Figure 3 – Result of the query which uses TREATAS() (Figure by the Author) 图 3 – 使用 TREATAS() 的查询结果（图片由作者提供）

In fact, Power BI uses this form all the time when it passes filters set in a report to the semantic model. But it does differently: It defines variables, sets the lineage and adds all filters directly to SUMMARIZECOLUMNS(): 事实上，当 Power BI 将报表中设置的筛选器传递给语义模型时，它一直在使用这种形式。但它的做法略有不同：它定义变量，设置血缘关系，并将所有筛选器直接添加到 SUMMARIZECOLUMNS() 中：

DEFINE VAR YearMonthFilter = TREATAS({ 202604 }, 'Date'[MonthKey])
VAR SelectedBrand = TREATAS( { "Adventure Works" }, 'Product'[BrandName])
EVALUATE 
SUMMARIZECOLUMNS(
    'Date'[Year]
    ,'Date'[MonthShortName]
    ,'Date'[MonthKey]
    ,'Product'[ProductCategoryName]
    ,YearMonthFilter
    ,SelectedBrand
    ,"Order Count", [Online Order Count]
)
ORDER BY 'Date'[MonthKey] ,'Product'[ProductCategoryName]

Clearing the lineage

清除血缘关系

You might encounter situations where you need to clear the lineage. The method for doing it varies depending on whether you have a single or multiple values as a filter. For example, look at the following code, where I use VALUE() to remove the lineage on the previous expression: 你可能会遇到需要清除血缘关系的情况。清除的方法取决于你拥有的是单个值还是多个值作为筛选器。例如，看看下面的代码，我使用 VALUE() 来移除之前表达式上的血缘关系：

DEFINE VAR YearMonthFilter = TREATAS({ 202604 }, 'Date'[MonthKey])
VAR YearMonthFilter_cleared = VALUE(YearMonthFilter)
EVALUATE 
CALCULATETABLE(
    SUMMARIZECOLUMNS(
        'Date'[Year]
        ,'Date'[MonthShortName]
        ,'Date'[MonthKey]
        ,'Product'[ProductCategoryName]
        ,"Order Count", [Online Order Count]
    )
    ,'Product'[BrandName] = "Adventure Works"
    ,YearMonthFilter_cleared
)
ORDER BY 'Date'[MonthKey] ,'Product'[ProductCategoryName]

This is the error delivered by Power BI: 这是 Power BI 报错的信息：

Figure 4 – Error Message when clearing the lineage with VALUES(). Without Lineage, we would need to add an equal filter as shown above in Figure 2 (Figure by the Author) 图 4 – 使用 VALUES() 清除血缘关系时的错误消息。如果没有血缘关系，我们需要像图 2 那样添加一个等式筛选器（图片由作者提供）

The engine cannot work with the filter in line 71 because it no longer has lineage. It will work in this form: 引擎无法处理第 71 行的筛选器，因为它不再具有血缘关系。它将以这种形式工作：

DEFINE VAR YearMonthFilter = TREATAS({ 202604 }, 'Date'[MonthKey])
VAR YearMonthFilter_cleared = VALUE(YearMonthFilter)
EVALUATE 
CALCULATETABLE(
    SUMMARIZECOLUMNS(
        'Date'[Year]
        ,'Date'[MonthShortName]
        ,'Date'[MonthKey]
        ,'Product'[ProductCategoryName]
        ,"Order Count", [Online Order Count]
    )
    ,'Product'[BrandName] = "Adventure Works"
    ,'Date'[MonthKey] = YearMonthFilter_cleared
)
ORDER BY 'Date'[MonthKey] ,'Product'[ProductCategoryName]

As you can see here, the query returns the same result as before: 正如你在这里看到的，查询返回了与之前相同的结果：

Figure 5 – Result of the query with the removal of the lineage and the change of the filter (Figure by the Author) 图 5 – 移除血缘关系并更改筛选器后的查询结果（图片由作者提供）

Notice the change of the filter argument in line 91. But there is a simpler way of clearing the lineage when working with measures. Look at the following query with the Measure [Order Count full year], which calculates the order count for the entire year: 注意第 91 行筛选器参数的变化。但在处理度量值时，有一种更简单的清除血缘关系的方法。看看下面带有度量值 [Order Count full year] 的查询，它计算了全年的订单数量：

DEFINE MEASURE 'All Measures'[Order Count full year] = 
VAR SelYear = TREATAS({ SELECTEDVALUE('Date'[Year]) }, 'Date'[Year])
RETURN 
CALCULATE([Online Order Count]
    ,REMOVEFILTERS('Date')
    ,SelYear
)
EVALUATE 
CALCULATETABLE(
    SUMMARIZECOLUMNS(
        'Date'[Year]
        ,'Date'[MonthShortName]
        ,'Date'[MonthKey]
        ,'Product'[ProductCategoryName]
        ,"Order Count", [Online Order Count]
        ,"Order Count full year", [Order Count full year]
    )
    ,'Product'[BrandName] = "Adventure Works"
)
ORDER BY 'Date'[MonthKey] ,'Product'[ProductCategoryName]

This is an extract of the result: 这是结果的摘录：

Figure 6 – Extract of query to calculate the order count for the entire year (Figure by the Author) 图 6 – 计算全年订单数量的查询摘录（图片由作者提供）

Now I add a scalar value to the variable: 现在我向变量添加一个标量值：

(Note: The original text ends abruptly here.) （注：原文在此处中断。）

← 返回新闻列表 / Back to news list