PowerBI 中的 Sumif 等效项不能与 DAX 函数一起正常工作 [英] Sumif equivalent in PowerBI doesn't work properly with DAX function

问题描述

我尝试在 PowerBI 中使用 SUMIF 等效项.我已经阅读了很多描述 CALCULATE 和 SUM 与 FILTER 函数结合使用的类似主题.但是他们都不能帮助我解决我的问题.

I try to use SUMIF equivalent in PowerBI. I have read already a lot of similar topics describing the usage of CALCULATE and SUM combined with FILTER functions. But none of them could help me with my problem.

我想做什么:

生产线在启动事件(例如机器启动、机器停止等)时将时间戳写入 CSV 文件.CSV 文件由 PowerQuery 处理，所有开始日期都在表的一列中可用.该列称为事件开始".

A production line writes a time stamp into a CSV file when it starts an event (e.g. machine starts, machine stopps etc.). The CSV file is processed by PowerQuery in a way that all start dates are available in one column of a table. The column is called "Event Start".

为了进一步分析机器的事件，我想添加另一列事件结束"，其中还包含每个事件的结束日期.CSV 文件未提供结束日期，但它等于下一个事件的开始日期.

To further analyze the events of the machine, I would like to add another column "Event End" that contains also the end date of each event. The end date is not provided by the CSV file but it is equal to the start date of the next event.

所以我需要做的是在第 n 行第 2 列中取一个值，该值取自第 n+1 行第 1 列.在 Excel 中，这样做很容易......一种解决方案(不是最好的 - 我知道)是将 SUMIF 与 Index 列和 Index+1 列一起使用:

So what I need to do is to have a value in row n, column 2 that is taken from row n+1, column 1. In Excel, very easy to do so... One solution (not the best one - I know) is to use SUMIF together with an Index column and Index+1 column:

我想出了使用 SUMIF 的想法，因为我知道有一个与 DAX 表达式(CALCULATE、SUM 和过滤器)等效的函数.不幸的是，在这种情况下，其他线程中给出的所有示例都不起作用.

I came up with the idea to use SUMIF because I know that there is an equivalent function with DAX expression (CALCULATE, SUM and filters). Unfortunately in this case all the examples given in other threads don't work.

有人可以帮我解决这个问题吗?也许有另一个 DAX 函数可以解决这个问题.

Could anybody help me with this? Maybe there is another DAX function available to solve this issue.

推荐答案

这里有两个选项.第一个返回 [Start Event]，其中 [Index] 比当前行中的 [Index] 高 1.

Here are two options. The first one returns the [Start Event] where [Index] is 1 higher than [Index] in the current row.

第二个返回大于当前行中[Start Event]的最小[Start Event].

The second one returns the smallest [Start Event] that is greater than the [Start Event] in the current row.

End Event =
CALCULATE (
    MAX ( 'Table1'[Start Event] ),
    FILTER ( 'Table1', 'Table1'[index] = EARLIER ( 'Table1'[index] ) + 1 )
)  

End Event 2 =
CALCULATE (
    MIN ( 'Table1'[Start Event] ),
    FILTER ( 'Table1', 'Table1'[Start Event] > EARLIER ( 'Table1'[Start Event] ) )
)

这篇关于PowerBI 中的 Sumif 等效项不能与 DAX 函数一起正常工作的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！