PowerPivot层次检测-活动项目

Question

在克里斯·坎贝尔的文章的基础上，我想添加一个额外的维度。我的表引用了他们自己的父级在一个去功能化的两层层次结构中.在调用了关联()函数之后，项目的主查找表可以如下所示：

Cases ID | Client | ParentMatterName | MatterName | ClaimAmount | OpenDate | CloseDate 1 | Mr. Smith | ABC Ltd | ABC Ltd | $40,000 | 1 Jan 15 | 4 Aug 15 2 | Mr. Smith | ABC Ltd | John | $0 |20 Jan 15 | 7 Oct 15 3 | Mr. Smith | ABC Ltd | Jenny | $0 | 1 Jan 15 | 20 Jan 15 4 | Mrs Bow | JQ Public | JQ Public | $7,000 | 1 Jan 15 | 4 Aug 15

我的目标是确定一个月有多少个家长小组开放。例如，在一月底，将有两个(ABC有限公司和JQ公众)。问题是，我还想报告一段时间内结束了多少件事情。只有在母公司的最后一件事结束时才算结束( ABC有限公司2015年10月7日)。所以我想要的输出是：

                 `| Jan 15 | Aug 15 | Oct 15 Count Matters Open  |   2    |   2    |   0 Count Opened        |   2    |   0    |   0 Count Closed        |   1    |   1    |   1` 

这是如何确定的呢？我能让每一行都知道其组中其他人的地位吗？

Count Matters Open:=CALCULATE (
DistinctCount(Cases[ParentMatterName]),
FILTER (
    MatterListView,
    [MatterOpenAtEnd] = TRUE()   ),

不起作用，因为它会计算每一个闭包，以及每一个待定的事情。

Answer 1

因此，你的口头描述似乎与你的输出样本不一致。然而，下面的措施适用于您所描述的需求，而且更为普遍。

基于这一要求

只有在母公司的最后一件事结束时才算结束( ABC有限公司2015年10月7日)。

我将两个新字段作为计算列添加到大小写中，以指示给定行是否代表父组中的第一个已打开的事项和表示最后一个已关闭的事项：

FirstOpened=
IF(
    [OpenDate] =
        CALCULATE(
            MIN('Cases'[OpenDate])
            ,ALLEXCEPT('Cases', 'Cases'[ParentMatterName])
        )
    ,1
    ,0
)

LastClosed:=
IF(
    [CloseDate] =
        CALCULATE(
            MAX('Cases'[CloseDate])
            ,ALLEXCEPT('Cases', 'Cases'[ParentMatterName])
        )
    ,1
    ,0
)

然后，我实施了以下措施：

CountMattersOpen:=
CALCULATE(
    DISTINCTCOUNT('Cases'[ParentMatterName])
    ,FILTER(
        VALUES('Cases'[OpenDate])
        ,'Cases'[OpenDate] <= MAX(DimDate[Date])
    )
    ,FILTER(
        VALUES('Cases'[CloseDate])
        ,'Cases'[CloseDate] >= MIN(DimDate[Date])
    )
)

CountOpened:=
CALCULATE(
    DISTINCTCOUNT('Cases'[ParentMatterName])
    ,FILTER(
        VALUES('Cases'[OpenDate])
        ,'Cases'[OpenDate] <= MAX(DimDate[Date])
            && 'Cases'[OpenDate] >= MIN(DimDate[Date])
    )
    ,'Cases'[FirstOpened] = 1
)


CountClosed:=
CALCULATE(
    DISTINCTCOUNT('Cases'[ParentMatterName])
    ,FILTER(
        VALUES('Cases'[CloseDate])
        ,'Cases'[CloseDate] <= MAX(DimDate[Date])
            && 'Cases'[CloseDate] >= MIN(DimDate[Date])
    )
    ,'Cases'[LastClosed] = 1
)

这些工作为任意(连续的)日期集，其中日历月代表一个小子集。如果您愿意的话，您可以在任意两个日期之间选择一个时间框架，而这些度量仍然可以正常工作。如果您已经将几个月放在透视表上，那么它将很好地工作在每个枢轴表行/列的月份边界上。

注意，这仅适用于date维度的存在，而date维度必须提供pivot表的行过滤器/列过滤器。不要在枢轴行/列中使用情况中的任何日期。

我们所做的就是使用FILTER()逐步遍历组成CasesCloseDate和CasesOpenDate的值列表，并筛选出那些逻辑上不属于pivot表强加的日期上下文的结果(并在必要时尊重FirstOpened/LastClosed )

同样，我遵循的是你的书面描述，而不是你的样本输出。

我的目标是确定一个月有多少个家长小组开放。

CountMattersOpen将为您提供在月底之前打开的、在月初(或任何任意时间框架)之后关闭的所有内容。这只是计算当前日期上下文中打开的所有内容。

CountOpened没有一个特定的定义，所以我将其与CountClosed相反--它计算出在当前日期上下文中拥有第一个OpenDate的ParentMatterNames数量。

只有在母公司的最后一件事结束时才算结束( ABC有限公司2015年10月7日)。

CountClosed给出了在当前日期上下文中关闭最后一个子MatterName的独立ParentMatterNames的数量。

下面是我的模型图的图像(这里没有必要使用BeginningOfMonth和EndOfMonth )：

​

​

Answer 2

抵消空白的一种可能方法是使用COUNTBLANK功能，并结合和和：

= IF ( AND ( Cases[CloseDate] = CALCULATE ( MAX ( Cases[CloseDate] ), ALLEXCEPT ( Cases, Cases[ParentName] ) ), CALCULATE ( COUNTBLANK ( Cases[CloseDate] ), ALLEXCEPT ( Cases, Cases[ParentName] ) ) = 0 ), 1, 0 )

虽然很复杂，但肯定是个更好的方法。