我可以比较PowerPivot中相邻行中同一列中的值吗？ [英] Can I compare values in the same column in adjacent rows in PowerPivot?

问题描述

我有一个PowerPivot表，我需要能够确定该表处于Error状态的时间。我的数据集如下所示：

我需要做的是查看ID和State列中的值，并查看上一行中的值是否为ERROR在状态列中，与在 ID列中相同。如果是这样，那么我需要计算这两行中更改日期值之间的差。

因此，例如，当我到达第4行时，看到上一行的第3行的状态列中的值为ERROR，并且上一行的ID列中的值与当前行相同，因此我将计算更改日期之间的差第3行和第4行中的值（对于此特定要求，我不在乎其他任何列中的值）。

有没有办法做到这一点在PowerPivot中？我已经做了大量的Internet搜索，看起来是否可以完成，它将使用EARLIER或EARLIEST DAX函数，但是我找不到任何可以告诉我如何甚至可以做到这一点的东西。

谢谢。

解决方案

克里斯，

我有很多次类似的要求，经过长时间的反复试验，我终于明白了（2013年）。

如前所述，请谨慎使用具有嵌套EARLIER和IF条件的计算列可能对性能要求太高。如果有更聪明的方法，我会很高兴看到它，但是就目前而言，这对我来说很好。

此外，请记住，所有计算出的列可以嵌套为1，但我将它们分开以使其更易于理解公式。

希望这会有所帮助：-）

I have a PowerPivot table for which I need to be able to determine how long an item was in an Error state. My data set looks something like this:

What I need to be able to do is to look at the values in the ID and State columns, and see if the value in the previous row is ERROR in the State column, and the same in the ID column. If it is, I then need to calculate the difference between the Changed Date values in those two rows.

So, for example, when I got to row 4, I would see that the value in the State column for Row 3, the previous row, is ERROR, and that the value in the ID column in the previous row is the same as the current row, so I would then calculate the difference between the Changed Date values in Row 3 and Row 4 (I don't care about the values in any of the other columns for this particular requirement).

Is there a way to do this in PowerPivot? I've done a fair amount of Internet searching, and it looks like if it can be done, it would use the EARLIER or EARLIEST DAX functions, but I can't find anything that tells me how, or even if, this can be done.

Thanks.

解决方案

Chris,

I have had similar requirements many times and after a really long time of trial-and-error, I finally understood how EARLIER works. It can be very powerful, but also very slow so always check for the performance of your calculations.

To answer your question, you will need to create 4 calculated columns:

1) Item Rank - used for ranking the issues with same Item ID

=COUNTROWS(FILTER('ID', EARLIER([Item ID]) = [Item ID] && EARLIER([Date]) >= [Date]))

2) Follows Error - to easily find issue that follows EROR issue

=IF([State] = "EROR",[Item Rank]+1)

3) Time of Following Issue - simple lookup so that you can calculate the different

=IF([Follows Error]>0, 
  LOOKUPVALUE([Date], [User], [User], [Item Rank], [Follows Error]), 
  BLANK()
)

4) Time Diff - calculation of time different for the specific issue

=IF([State]="EROR",
  DAY([Time of Following Issue])-DAY([Date]),
  BLANK()
)

With those calculated columns, you can then easily create a powerpivot table, drag State and Item Id onto the ROWS pane and then simply add Time Diff to Values. You will get an overview of issues that contain string "EROR" issue and the time it took to resolve them.

This is what it looks like in PowerPivot window:

And the resulting Pivot table:

You can download my Excel file here (2013).

As I mentioned, be careful with the performance as the calculated columns with nested EARLIER and IF conditions might be a bit too performance-demanding. If there is a smarter way, I would be very happy to see it, but for now this works for me just fine.

Also, keep in mind that all calculated columns could be nested into 1, but I kept them separated to make it easier to understand the formulas.

Hope this helps :-)

这篇关于我可以比较PowerPivot中相邻行中同一列中的值吗？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！