如何在DAX中应用带有两个过滤器的Calculate函数以获取运行总和 [英] how to apply Calculate function with two filters in DAX to get the Running Sum

问题描述

我想获得 [到期天数] 的按项目编号分组的运行总和，对于每个项目编号，请获取每个阶段/结束日期的值。
在SQL中，我会这样做：

I would like to get the running sum of [Days Past Due] Column grouped by project number and for each project number, get the value for each phase/end date. IN SQL I would do:

SELECT   Project_number
         ,phase
         ,Sum(Days Past Due) RunningTotal
FROM     Table

GROUP BY Project_number
         ,phase
ORDER BY Project_number
         ,phase

请在DAX中执行类似的操作。

I would do something similar in DAX please.

我尝试：

CALCULATE (
    SUM ( DataSource[Days Past Due] ),
    ALLEXCEPT ( DataSource, DataSource[Project Number] )
)

它为我提供了4行中重复的每个项目的总数。那不是我想要的我需要对此应用第二个过滤器。

It gives me the total for each project number repeated in the 4 rows. That's not what I want exactly. I need to apply a second filter on that.

请查看附件，它的最后一列具有所需的输出。

Please look at the attachment, it has the last column with the desired output.

在此先感谢您的建议。

Thank you in advance for your suggestions.

推荐答案

您可以这样调整自己的尝试：

You can adjust your attempt like this:

Cumulative Days Past Due = 
CALCULATE(
    SUM( DataSource[Days Past Due] ),
    FILTER(
        ALLEXCEPT( DataSource, DataSource[Project Number] ),
        DataSource[End Date] <= MAX( DataSource[End Date] )
    )
)

请注意，您可以在 FILTER 函数，通过使用&& 加入更多条件。您还可以在 CALCULATE 中添加更多过滤条件。下面是一个示例：

Note that you can include more filtering conditions in the FILTER function by joining more conditions with &&. You can add more filter conditions inside CALCULATE as well. Here's an example:

Cumulative Days Past Due = 
CALCULATE(
    SUM( DataSource[Days Past Due] ),
    FILTER(
        ALLEXCEPT( DataSource, DataSource[Project Number] ),
        DataSource[End Date] <= MAX( DataSource[End Date] )
            && DataSource[Start Date] > DATE( 2018, 12, 31 )
    ),
    DataSource[Phase] = "Scope"
)

---

CALCULATE 和 FILTER 可能对进一步说明有用：

---

The documentation for CALCULATE and FILTER might be useful for further clarification:

https://dax.guide/calculate/

https://docs.microsoft.com/en-us/dax/calculate-function-dax

https://dax.guide/filter/

https://docs.microsoft.com/en-us/dax/filter-function-dax

如果您尝试在计算列中编写此公式，则该公式将无法正常工作，您需要使用 EARLIER 代替 MAX ：

If you are trying to write this formula in a calculated column, it will not work as expected and you will need to use EARLIER instead of MAX:

Cumulative Days Past Due = 
CALCULATE(
    SUM( DataSource[Days Past Due] ),
    FILTER(
        ALLEXCEPT( DataSource, DataSource[Project Number] ),
        DataSource[End Date] <= EARLIER( DataSource[End Date] )
    )
)

其原因是，在度量 MAX 中是在其本地过滤器上下文中计算的，而在计算列中，上下文是不同的并且您使用 EARLIER 来引用较早的行上下文，即，当前行的结束日期较大的表格（而不是较小的 FILTER 表）。

The reason for this is that in a measure MAX is calculated within its local filter context but in a calculated column the context is different and you use EARLIER to refer to the earlier row context, that is, the End Date in the current row of the larger table (rather than the smaller FILTER table).

如果您想要某种可以用作度量或计算列的东西，那么@RADO的解决方案非常接近，您可以编写以下代码：

If you want something that works either as a measure or a calculated column, then @RADO's solution is pretty close and you can write this:

Cumulative Days Past Due =
VAR CurrDate =
    CALCULATE( MAX( DataSource[Start Date] ) )
RETURN
    CALCULATE(
        SUM( DataSource[Days Past Due] ),
        ALLEXCEPT( DataSource, DataSource[Project Number] ),
        DataSource[End Date] <= CurrDate
    )

在此版本中， MAX 的工作方式与上面的第一个版本相同，它也应该以计算得出的方式工作，因为将 MAX 包裹在<$ c $中c> CALCULATE 执行上下文转换，将行上下文转换为与该单行相对应的过滤器上下文。

In this version, the MAX works just as it did before in the first version above and it should also work in a calculated measure because wrapping the MAX in a CALCULATE performs a context transition that transforms the row context into a filter context corresponding to that single row.

您可以删除 CALCULATE 包装器仅作一个度量，并同时删除 CALCULATE 和 MAX 函数可简化计算列。在这两种情况下，此变量版本都可能是性能更高的版本，因为 ALLEXCEPT 函数已经过优化，可以在 CALCULATE 而不是在使用 FILTER 时实例化新表。

You can drop the CALCULATE wrapper for just a measure and drop both the CALCULATE and MAX functions for a simpler calculated column. In either of these cases, this variable version is likely the more performant one since the ALLEXCEPT function is optimized to work efficiently within CALCULATE rather than having to instantiate a new table when using FILTER.

有关累计总数的更多详细信息，我建议使用DAX模式作为资源：

For further details on cumulative totals, I recommend DAX Patterns as a resource:

> https://www.daxpatterns.com/cumulative-total/

这篇关于如何在DAX中应用带有两个过滤器的Calculate函数以获取运行总和的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！