DAX Measure 计算聚合数据，但按案例 ID 分组 [英] DAX Measure to calculate aggregate data, but group by Case ID

问题描述

所以我有一个变量

var varSubItem = CALCULATE (MAX(Outages[SubItem]), Outages[DATE] >= DATE(2019, 07, 14) )

计算出在 1 天内发生故障的项目.见下文.

to calculate out items that have had an outage within 1 day. See below.

然后我有另一个变量

var data =
CALCULATE (
    COUNT ( Outages[CASE_ID] ),
    ALLSELECTED ( Outages ),
    Outages[SubItem] = devices
)

这让我返回了过去 2 年中设备的中断次数.这只是最近两年，因为我的表格视觉对象有一个针对该时间范围的过滤器.

which gives me back the outage count for the devices in the last 2 years. It's only the last two years because my table visual has a filter for that time frame.

我祈祷我说得通，因为我已经尝试这样做 2 周了.

I pray that I'm making sense because I have been trying to do this for 2 weeks now.

Devices w Outages 2Yr =
VAR devices =
    CALCULATE ( MAX ( Outages[DEVICE_ID] ), Outages[DATE] >= DATE ( 2019, 07, 14 ) )
VAR data =
    CALCULATE (
        COUNT ( Outages[CASE_ID] ),
        ALLSELECTED ( Outages ),
        Outages[DEVICE_ID] = devices
    )
RETURN data

我明白了，

| Area   | Item | SubItem | Case      | Date            | Outage Count |
|--------|------|---------|-----------|-----------------|--------------|
| XXXXX' | ABC1 | 123A    | 123456789 | 7/14/19 1:15 AM | 1            |
|        | ABC2 | 123B    | 132456798 | 7/14/19 3:20 AM | 1            |
|        | ABC3 | 123C    | 984561325 | 7/14/19 6:09 PM | 1            |
|        | ABC4 | 123D    | 789613453 | 7/14/19 3:54 PM | 3            |
|        | ABC5 | 123E    | 335978456 | 7/14/19 2:10 PM | 2            |
| Total  |      |         |           |                 | 8            |

当我应该得到这个时，

| Area   | Item | SubItem | Case      | Date            | Outage Count |
|--------|------|---------|-----------|-----------------|--------------|
| XXXXX' | ABC1 | 123A    | 123456789 | 7/14/19 1:15 AM | 1            |
|        | ABC2 | 123B    | 132456798 | 7/14/19 3:20 AM | 1            |
|        | ABC3 | 123C    | 984561325 | 7/14/19 6:09 PM | 1            |
|        | ABC4 | 123D    | 789613453 | 7/14/19 3:54 PM | 1            |
|        | ABC4 | 123D    | 789613211 | 4/19/18 4:20 AM | 1            |
|        | ABC4 | 123D    | 789611121 | 9/24/17 5:51 AM | 1            |
|        | ABC5 | 123E    | 335978456 | 7/14/19 2:10 PM | 1            |
|        | ABC5 | 123E    | 335978111 | 2/21/19 7:19 AM | 1            |
| Total  |      |         |           |                 | 8            |

推荐答案

我觉得你想要的更接近这个:

I think what you want is closer to this:

Devices w Outages 2Yr =
VAR devices =
    CALCULATETABLE (
        VALUES ( Outages[SubItem] ),
        ALLSELECTED ( Outages ),
        Outages[DATE] >= TODAY() - 1
    )
RETURN
    CALCULATE (
        COUNT ( Outages[Case] ),
        FILTER ( Outages, Outages[SubItem] IN devices )
    )

这将创建一个 SubItem 值列表，而不是您使用 MAX 获得的单个值，这就是您的 ALLSELECTED 函数需要使用的地方.

This creates a list of SubItem values rather than the single one you get with MAX and that's where your ALLSELECTED function needs to go.

要总计在 SubItem 级别尝试此调整:

To total at the SubItem level try this tweak:

Devices w Outages 2Yr =
VAR devices =
    CALCULATETABLE (
        VALUES ( Outages[SubItem] ),
        ALLSELECTED ( Outages ),
        Outages[DATE] >= TODAY() - 1,
        VALUES ( Outages[SubItem] )
    )
RETURN
    CALCULATE (
        COUNT ( Outages[Case] ),
        ALLSELECTED ( Outages ),
        Outages[SubItem] IN devices
    )

这里的确切逻辑对于初学者 DAX 用户来说有点复杂，但请记住，DAX 都是关于过滤器的.

The exact logic here is a bit complex for a beginner DAX user, but just keep in mind that DAX is all about filters.

对于变量 devices，我们想要一个包含当前上下文中所有 SubItem 值的列表，这些值受日期约束.CALCULATETABLE 函数允许我们修改过滤器上下文.ALLSELECTED 函数是一个表格过滤器，用于从视觉对象中删除任何过滤器上下文，以便所有未被切片器过滤掉的 Date 和 Case 值或页面/报告级别过滤器包括在内.否则，对于日期早于 TODAY()-1 的行，您会得到空白.日期值布尔过滤是不言自明的，但是我在最后添加了另一个表过滤器 VALUES(Outages[SubItem])，以添加回 SubItem 上下文视觉.

For the variable devices, we want a list of all SubItem values in the current context subject to a date constraint. The CALCULATETABLE function allows us to modify our filter context. The ALLSELECTED function is a table filter removes any filter context from the visual so that all Date and Case values that aren't filtered out by slicers or page/report level filters are included. Otherwise, you'd get blanks for rows that have dates before TODAY()-1. The date value boolean filtering is self-explanatory, but then I add another table filter at the end, VALUES(Outages[SubItem]), to add back the SubItem context from the visual.

CALCULATE 部分功能类似.在更改过滤器上下文以删除 Case 和 Date 上的过滤器上下文后，我们计算所有 Case 值，并且仅采用 SubItem 变量中生成的列表中的值.

The CALCULATE piece functions similarly. We count all the Case values after altering the filter context to remove filter context on Case and Date and only taking SubItem values from the list generated in the variable.

这篇关于DAX Measure 计算聚合数据，但按案例 ID 分组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！