仅根据过滤器上下文计算多个的最新实例 [英] Counting latest instance of multiple only based on filter context

问题描述

我有大量的车辆清单中发生的事件，这些事件会影响它们的使用状态或停用状态。我想创建一个可以根据此表中的事件在任何时间点计算各种清单中车辆数量的度量。

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

以下是一些示例数据 event_list ：

  vehicle_id event_date event event_sequence存货
 100 2018-01-01购买1个在役
 101 2018-01-01购买1个在役
 102 2018-02-04购买1服务中
 100 2018-02-07维护2服务中
 101 2018-02-14损坏2服务中
 101 2018-02-18修复3服务中的
 100 2018-03-15修复3服务中的
 102 2018-05-01损坏2服务中的
 103 2018-06-03购买1服务中的
  

我希望能够在Excel中创建数据透视表（或使用CUBE函数等）以获取如下所示的输出表：

  date in service in service 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标签，因为我现在正在使用该软件来尝试解决此问题。

解决方案

我认为我找到了一种比以前更干净的方法。

>

---

让我们在 event_list 表中添加两列。一项计算该日期服务中 的车辆，另一种统计服务中断中的车辆

  InService = 
 VAR摘要= SUMMARIZE（
 FILTER（event_list，
 event_list [event_date]< = EARLIER（event_list [event_date]）），
 event_list [vehicle_id]，
 MaxSeq，MAX（event_list [event_sequence]））
 
 VAR过滤= FILTER（event_list，
 event_list [event_sequence] = 
 MAXX（
 FILTER（摘要，
 event_list [vehicle_id] = EARLIER（event_list [vehicle_id]）），
 [MaxSeq]））
 
 RETURN SUMX（已过滤，1 *（event_list [inventory] ​​=在使用中））
  

您可以为 OutOfService <创建类似的计算列/ code>，或者您可以将总数减去 InService 计数。

  OutOfService = 
 CALCULATE（
 DISTINCTCOUNT（event_list [vehicle_id]），
 FILTER（event_list，
 event_list [event_date]< = EARLIER（event_list [event_date] ）））
-event_list [InService] 
  

---

这是背后的逻辑C累积列 InService ：

我们首先创建一个摘要该表可计算每辆车的最大 event_sequence 值。 （我们过滤 event_date 仅考虑到当前使用的日期。）

现在，我们知道每辆车的最后一个 event_sequence 值是什么，我们用它来过滤整个表格，使其仅向下过滤到与这些车辆和序列值相对应的行。筛选器逐行通过表格，检查序列值是否与我们在 Summary 表中计算出的值匹配。请注意，当我们将 Summary 表过滤为仅用于当前正在使用的车辆时，我们只会得到一行。我只是使用 MAXX 提取 [MaxSeq] 值。 （有点像使用 LOOKUPVALUE ，但是您不能在变量上使用它。）

现在我们已将表格仅过滤为每辆车的最新事件，我们要做的就是计算其中有多少是在使用中 。我在这里使用 SUMX ，其中 1 *（True / False）强制布尔值返回 1 或 0 。

I've got a large table of events that have occurred in an inventory of vehicles, which affect whether they are in service or out of service. I would like to create a measure that would be able to count the number of vehicles in the various inventories at any point in time, based on the events in this table.

This table is pulled from a SQL database into an Excel 2016 sheet, and I'm using PowerPivot to try to come up with the DAX measure.

Here is some example data 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

I'd like to be able to create a pivot table in Excel (or use CUBE functions, etc) to get an output table like this:

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

Essentially, I want to be able to calculate the inventory based on any date in time. The example only has a few dates, but hopefully provides enough of a picture.

I've basically come up with this so far, but it counts more vehicles than desired - I can't figure out how to only take the latest event_sequence or event_date and use that to count the inventory.

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])
)

I tried using MAX() and EARLIER() functions, but they don't seem to work.

Edit: Added the PowerBI tag as I'm now using that software to attempt to solve this as well. See comments on Alexis Olson's answer.

解决方案

I think I've found a much cleaner method than I gave previously.

---

Let's add two columns onto the event_list table. One which counts vehicles "in-service" on that date and one which counts vehicles "out-of-service" on that date.

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"))

You can create an analogous calculated column for OutOfService or you can just take the total minus the InService count.

OutOfService =
    CALCULATE(
        DISTINCTCOUNT(event_list[vehicle_id]),
        FILTER(event_list,
            event_list[event_date] <= EARLIER(event_list[event_date])))
    - event_list[InService]

Now all you have to do is put event_date on the matrix visual rows section and add the InService and OutOfService columns to the values section (use Maximum or Minimum for the aggregation option rather than Sum).

---

Here's the logic behind the calculated column InService:

We first create a Summary table which calculates the maximal event_sequence value for each vehicle. (We filter the event_date to only consider dates up to the current one we are working with.)

Now that we know what the last event_sequence value is for each vehicle, we use that to filter the entire table down to just the rows that correspond to those vehicles and sequence values. The filter goes through the table row by row and checks to see if the sequence value matches the one we calculated in the Summary table. Note that when we filter the Summary table to just the vehicle we are currently working with, we only get a single row. I'm just using MAXX to extract the [MaxSeq] value. (It's kind of like using LOOKUPVALUE, but you can't use that on a variable.)

Now that we've filtered the table just to the most recent events for each vehicle, all we need to do is count how many of them are "in-service". I used a SUMX here where the 1*(True/False) coerces the boolean value to return 1 or 0.

这篇关于仅根据过滤器上下文计算多个的最新实例的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！