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
)

这让我回溯了过去两年中设备的停机次数。这只是最近两年，因为我的桌面视觉在该时间范围内有一个筛选器。

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            |

推荐答案

我认为您想要的更接近此： / p>

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.

对于变量设备，我们需要一个受日期约束的当前上下文中所有 SubItem 值的列表。 CALCULATETABLE 函数允许我们修改过滤器上下文。 ALLSELECTED 函数是一个表过滤器，它从视觉上删除任何过滤器上下文，以便所有 Date 和 Case 值。否则，对于日期在 TODAY（）-1 之前的行，您将获得空白。日期值布尔过滤是不言自明的，但随后我在末尾添加了另一个表过滤器 VALUES（Outages [SubItem]），以重新添加<$ c $视觉上的c> 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 和上的过滤器上下文之后，我们计算所有 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屋！