powerbi - DAX 测量 : project duration (days) from dimension starting & ending date

Question

我有以下场景，已稍微简化:

成本事实表:

date, project_key, costs €

项目维度:

project_key, name, starting date, ending date

日期维度:

date, years, months, weeks, etc

我需要创建一个度量，使用项目维度的开始和结束日期来告诉项目持续时间。第一个挑战是事实表中并非所有日期都没有交易。项目开始日期可能是 1 月 1 日，但第一个成本交易记录在事实表上，例如 1 月 15 日。因此，如果在过滤器上下文中，我们仍然需要计算开始日期和结束日期之间的天数。

因此，第二挑战是过滤器上下文。用户可能只想查看二月。因此，它的项目开始日期是 1.6.2016，结束日期是 1.11.2016，用户只想查看 9 月，它应该只显示 30 天。

第三个挑战是查看多个项目的天数。因此，如果用户仅选择一天，则应该查看所有正在进行的项目的计数。

我感谢任何可能导致解决方案的帮助。因此，如果需要，请随时询问更多详细信息。

编辑:这里有一张图片可以更好地解释这一点:

更新2017年2月7日

仍在尝试为此解决方案创建单一措施。衡量哪些用户可以仅使用日期、项目或按原样使用。每天正在进行的项目计数的单独计算列将是简单的解决方案，但它只能按日期表进行过滤。

2017 年 2 月 9 日更新

感谢大家的努力。作为最终结果，我相信不基于事实表的计算是相当棘手的。对于这个具体案例，我最终使用 CROSS JOIN 来创建日期和项目 ID 的新表来满足所有要求。一种选择是以零成本将开始日期和结束日期作为自己的行添加到事实表中。真正的解决方案还有更多的维度需要我们考虑。

Answer 1

要获得预期结果，您必须创建一个计算列和一个度量，计算列可以计算项目执行日期中的项目数，度量可以计算从 [starting_date] 过去的天数] 和 [ending_date] 在每个项目中考虑帐户过滤器。

必须使用以下表达式在 dim_date 表中创建计算列:

Count of Projects =
SUMX (
    FILTER (
        project_dim,
        [starting_date] <= EARLIER ( date_dim[date] )
            && [ending_date] >= EARLIER ( date_dim[date] )
    ),
    1
)

应使用以下表达式在 project_dim 表中创建度量:

Duration (Days) =
DATEDIFF (
    MAX ( MIN ( [starting_date] ), MIN ( date_dim[date] ) ),
    MIN ( MAX ( [ending_date] ), MAX ( date_dim[date] ) ),
    DAY
)
    + 1

你将得到的结果是这样的:

如果您使用切片器或 dim_date 表上的过滤器来过滤一周

更新

支持 SSAS 2014 - DATEDIFF()在 SSAS 2016 中可用。

首先，重要的是您要意识到您正在测量两个不同的事物，但您只希望用户可以看到其中一个测量值。在第一个预期结果中，您希望获取每个日期运行的项目数量，而在预期结果 2 和 3(在 OP 中)中，您希望每个项目经过的天数考虑 date_dim 上的过滤器>.

您可以创建一个度量将两个度量包装在一个度量中，并使用 HASONEFILTER 来确定每个度量应运行的上下文。在继续进行包装措施之前，请检查以下措施，该措施将替换上面使用 DATEDIFF 函数发布的措施，该函数在您的环境中不起作用。

创建确定每个日期的项目数所需的先前计算列后，创建一个名为持续时间度量的度量，您的用户不会使用此度量，但可以让我们计算最终措施。

Duration Measure = SUMX(FILTER (
        date_dim,
        date_dim[date] >= MIN ( project_dim[starting_date] )
            && date_dim[date] <= MAX ( project_dim[ending_date] )
    ),1
)

现在用户应该交互的最终度量可以写成这样:

Duration (Days) =
IF (
    HASONEFILTER ( date_dim[date] ),
    SUM ( date_dim[Count of Projects] ),
    [Duration Measure]
)

此度量将确定上下文，并将返回给定上下文的正确度量。因此，您可以为两个表添加相同的度量，它将返回所需的结果。

尽管此解决方案在 Power BI 中进行了演示，但它也适用于 Power Pivot。