首页 文章

DAX基于3列运行总计，其中一列是重复整数运行总计

提问于
浏览 次
1

这是DAX / PowerPivot的新手，并且在第一天遇到了极其棘手的问题 .

我有一些数据（90,000行）我试图用来计算民间工作班次的累积疲劳分数（使用PowerPivot / Excel 2016） . 根据下面的屏幕截图，数据集是多个员工的班次数据，它具有累计工作天数和休假天数，每当他们从一个州切换到另一个州时重置为1，以及一个“分数”列 . 我的 生产环境 数据包含了它们的疲劳程度 .

我想累计总结疲劳评分，并在“工作日”和“休息日”状态之间移动时重置它 . 我想要的输出位于最右边的“Desired”列中，我使用绿色突出显示工作天数和休假天数，并在单独的Emp_ID块周围放置一个粗体边框以帮助演示数据 .

enter image description here

我的问题和DAX running total (or count) across 2 groups的SO帖子之间有一些相似之处，除了我的一个列（即累积日期1）是从1到x的重复序列 . 如果我今天获得了Javier Guillén's post，那么Javier Guillén's post可能会成为一个很好的起点 .

鉴于我是DAX新手（我的背景是VBA，SQL和Excel公式），我几乎无法开始概念化DAX需要的样子 . 但是为了避免有人因为我甚至没有提供起点而指责我，我试图调整以下DAX，而不是真正弄清楚我在做什么：

Cumulative:=CALCULATE( 
SUM( Shifts[Score] ) , 
FILTER(Shifts,Shifts[Cumulative Days] <= VALUES(Shifts[Cumulative Days] )) , 
ALLEXCEPT( shifts, Shifts[Workday],Shifts[EMP_ID] ) )

现在我将是第一个承认这个代码是Infinite Monkey Theorem的DAX equivelant的人 . 唉，我今天没有香蕉，我唯一的希望是有人发现这个问题适当地剥皮了 .

powerpivot dax

1 回答

  • 0

    此表的问题是无法确定何时执行累计总计时停止求和 .

    我认为实现这一目标的一种方法可能是计算连续工作日状态发生变化的下一个第一个日期 .

    例如， EMP_ID 70073的前三行中的工作日状态是相同的，直到第四行，日期 04-May ，即工作日状态更改的日期 . 我的想法是创建一个计算列，找到每个工作日系列的状态更改日期 . 该列允许我们实现累积总和 .

    下面是名为 Helper 的计算列的表达式 .

    Helper =
IF (
    ISBLANK (
        CALCULATE (
            MIN ( [Date] ),
            FILTER (
                'Shifts',
                'Shifts'[EMP_ID] = EARLIER ( 'Shifts'[EMP_ID] )
                    && 'Shifts'[Workday] <> EARLIER ( 'Shifts'[Workday] )
                    && [Date] > EARLIER ( 'Shifts'[Date] )
            )
        )
    ),
    CALCULATE (
        MAX ( [Date] ),
        FILTER (
            Shifts,
            Shifts[Date] >= EARLIER ( Shifts[Date] )
                && Shifts[EMP_ID] = EARLIER ( Shifts[EMP_ID] )
        )
    )
        + 1,
    CALCULATE (
        MIN ( [Date] ),
        FILTER (
            'Shifts',
            'Shifts'[EMP_ID] = EARLIER ( 'Shifts'[EMP_ID] )
                && 'Shifts'[Workday] <> EARLIER ( 'Shifts'[Workday] )
                && [Date] > EARLIER ( 'Shifts'[Date] )
        )
    )
)

    简而言之，表达式表示如果当前工作日系列更改的日期计算返回 blank ，则使用该EMP_ID的最后日期和一个日期 .

    请注意，无法计算上一个工作日系列的更改日期，在本例中为 08-May 行，因此如果计算返回空白，则表示它正在最后一个系列中进行评估，那么我的表达式应该返回该 EMP_ID 的最大日期加一天 .

    计算出的列在表中后，您可以使用以下表达式为累积值创建度量：

    Cumulative Score =
CALCULATE (
    SUM ( 'Shifts'[Score] ),
    FILTER ( ALL ( 'Shifts'[Helper] ), [Helper] = MAX ( [Helper] ) ),
    FILTER ( ALL ( 'Shifts'[Date] ), [Date] <= MAX ( [Date] ) )
)

    在Power BI的表格中（我无法访问 PowerPivot 至少八小时），结果如下：

    enter image description here

    我认为有一个更简单的解决方案，我的第一个想法是使用变量，但这仅在DAX 2015中支持，很可能你没有使用Excel 2016 .

    UPDATE: 在度量计算中只保留一个过滤器 . FILTER是整个表中的迭代器，因此只使用一个过滤器和逻辑运算符可能会更高效 .

    Cumulative Score =
CALCULATE (
    SUM ( 'Shifts'[Score] ),
    FILTER (
        ALL ( 'Shifts'[Helper], Shifts[Date] ),
        [Helper] = MAX ( [Helper] )
            && [Date] <= MAX ( [Date] )
    )
)

    UPDATE 2: 透视表（矩阵）的解决方案，因为前一个表达式仅适用于表格可视化 . 此外，度量表达式已经过优化，只能实现一个过滤

    这应该是数据透视表的最终表达式：

    Cumulative Score =
CALCULATE (
    SUM ( 'Shifts'[Score] ),
    FILTER (
        ALLSELECTED ( Shifts ),
        [Helper] = MAX ( [Helper] )
            && [EMP_ID] = MAX ( Shifts[EMP_ID] )
            && [Date] <= MAX ( Shifts[Date] )
    )
)

    注意：如果要忽略过滤器，请使用ALL而不是ALLSELECTED .

    结果 Power BI 矩阵：

    enter image description here

    结果在 PowerPivot 数据透视表中：

    enter image description here

    如果这有帮助，请告诉我 .

    回复于

相关问题