仅根据过滤器上下文计算多个最新实例-Java 学习之路

我有一张大型事件表，这些事件发生在车辆库存中，这会影响他们是在服务还是在服务中 . 我想根据此表中的事件创建一个能够在任何时间点计算各种库存中车辆数量的度量 .

此表从SQL数据库中提取到Excel 2016工作表中，我正在使用PowerPivot尝试提出DAX度量 .

以下是一些示例数据 event_list ：

vehicle_id    event_date    event         event_sequence    inventory
100           2018-01-01    purchase      1                 in-service
101           2018-01-01    purchase      1                 in-service
102           2018-02-04    purchase      1                 in-service
100           2018-02-07    maintenance   2                 out-of-service
101           2018-02-14    damage        2                 out-of-service
101           2018-02-18    repaired      3                 in-service
100           2018-03-15    repaired      3                 in-service
102           2018-05-01    damage        2                 out-of-service
103           2018-06-03    purchase      1                 in-service

我希望能够在Excel中创建一个数据透视表（或使用CUBE函数等）来获取这样的输出表：

date          in-service     out-of-service
2018-02-04    3              0
2018-02-14    1              2
2018-03-15    3              0
2018-06-03    3              1

基本上，我希望能够根据任何日期计算库存 . 这个例子只有几个日期，但希望提供足够的图片 .

到目前为止，我基本上已经提出了这个问题，但它计算的车辆数量超出预期 - 我无法弄清楚如何只采用最新的event_sequence或event_date并使用它来计算库存 .

cumulative_vehicles_at_date:=CALCULATE(
    COUNTA([vehicle_id]),
    IF(IF(HASONEVALUE (event_list[event_date]), VALUES (event_list[event_date]))>=event_list[event_date],event_list[event_date])
)

我尝试使用MAX（）和EARLIER（）函数，但它们似乎不起作用 .

编辑：添加了PowerBI标签，因为我现在正在使用该软件尝试解决此问题 . 请参阅Alexis Olson的回答评论 .

2 回答

0
我想我找到了比以前更清洁的方法 .

让我们在 event_list 表上添加两列 . 一个在该日期计算车辆 "in-service" ，在该日期计算车辆 "out-of-service" 的一个 .
```
InService = 
    VAR Summary = SUMMARIZE(
                      FILTER(event_list,
                          event_list[event_date] <= EARLIER(event_list[event_date])),
                      event_list[vehicle_id],
                      "MaxSeq", MAX(event_list[event_sequence]))

    VAR Filtered = FILTER(event_list,
                       event_list[event_sequence] =
                           MAXX(
                               FILTER(Summary,
                                   event_list[vehicle_id] = EARLIER(event_list[vehicle_id])),
                               [MaxSeq]))

    RETURN SUMX(Filtered, 1 * (event_list[inventory] = "in-service"))
```
您可以为 OutOfService 创建类似的计算列，也可以将总减去 InService 计数 .
```
OutOfService =
    CALCULATE(
        DISTINCTCOUNT(event_list[vehicle_id]),
        FILTER(event_list,
            event_list[event_date] <= EARLIER(event_list[event_date])))
    - event_list[InService]
```
现在，您只需将 event_date 放在矩阵可视行部分上，并将 InService 和 OutOfService 列添加到值部分（使用聚合选项的最大值或最小值而不是总和） .

这是计算列 InService 背后的逻辑：

我们首先创建一个 Summary 表，计算每辆车的最大 event_sequence 值 . （我们过滤 event_date 只考虑我们正在使用的当前日期 . ）

现在我们知道每辆车的最后一个 event_sequence 值是什么，我们使用它来将整个表格过滤到与这些车辆和序列值对应的行 . 过滤器逐行遍历表，并检查序列值是否与我们在 Summary 表中计算的序列值相匹配 . 请注意，当我们将 Summary 表过滤到我们当前使用的车辆时，我们只获得一行 . 我只是使用 MAXX 来提取 [MaxSeq] 值 . （这有点像使用 LOOKUPVALUE ，但你不能在变量上使用它 . ）

现在我们已经将表格过滤到每辆车的最新事件，我们需要做的就是计算其中有多少是 "in-service" . 我在这里使用 SUMX ，其中 1*(True/False) 强制布尔值返回 1 或 0 .
回复于 2024-04-26T12:51:33+08:00
0
这非常困难 . 我没有一个很好的答案，但这里有一些有用的东西 .

您将创建一个新的计算表，您可以在其中计算每个日期的每辆车的状态 . 从每辆车和每个日期的基本交叉连接开始：
```
= CROSSJOIN(VALUES(event_list[vehicle_id]), VALUES(event_list[event_date]))
```
然后添加计算列以查找该日期每辆车的最大序列号 .
```
Sequence = MAXX(
               FILTER(event_list,
                   event_list[event_date] <= Cross[event_date] &&
                   event_list[vehicle_id] = Cross[vehicle_id]),
               event_list[event_sequence])
```
现在，您可以使用另一个计算列查找每个车辆/序列对的库存值：
```
Inventory = LOOKUPVALUE(
                event_list[inventory],
                event_list[vehicle_id], Cross[vehicle_id],
                event_list[event_sequence], Cross[Sequence])
```
结果应如下所示：

完成此操作后，您可以使用此计算表创建矩阵 . 将 event_date 放在行上，将 Inventory 放在列上 . 过滤掉可视化级别过滤器中的空白库存值，并将 vehicle_id 放在值字段中，使用计数或非重复计数作为聚合方法（而不是默认总和） .

它应该如下所示：
回复于 2024-04-26T12:51:33+08:00

仅根据过滤器上下文计算多个最新实例

2 回答

相关问题