Power Bi/Dax:使用过滤器汇总表 [英] Power Bi/Dax: Summarize table with filters

问题描述

我有这张桌子:

IP  Technology  Status
IP1 T1  Passed
IP1 T1  Passed
IP1 T1  Failed
IP1 T2  Failed
IP1 T2  Failed
IP1 T2  Passed
IP2 T3  Passed
IP2 T3  Failed
IP3 T4  Passed
IP3 T4  Passed
IP3 T5  Passed
IP3 T5  Passed
IP3 T5  Passed
IP3 T5  Passed
IP3 T5  Passed
IP2 T6  Passed
IP2 T6  Passed
IP2 T6  Passed
IP2 T6  Passed

我必须删除一些技术(T2 和 T6)并显示以下汇总表:(我只对通过"结果感兴趣，但留下失败"列以供参考)

I have to remove some of the Technology (T2 and T6) and show the following summary table: (I am interested only in the ‘Passed’ results but left the ‘failed’ column for reference)

IP   Failed Passed  100% Passed
IP1  33%    67%     No
IP2  50%    50%     No
IP3  0%     100%    Yes

这就是我所做的:我创建了两个临时表来计算通过的测试数和测试总数:

This is what I did: I created two temporary tables to calculate the number of passed tests and the total number of tests:

Table1 =
CALCULATETABLE (
    GROUPBY (
        'Table',
        'Table'[IP],
        'Table'[Status],
        "#Passed", COUNTAX ( CURRENTGROUP (), ( 'Table'[Status] = "Passed" ) )
    ),
    'Table'[Technology] = "T1"
        || 'Table'[Technology] = "T3"
        || 'Table'[Technology] = "T4"
        || 'Table'[Technology] = "T5"
)

Test2 =
CALCULATETABLE (
    GROUPBY (
        'Table',
        'Table'[IP],
        "#scan", COUNTAX ( CURRENTGROUP (), ( 'Table'[IP] ) )
    ),
    'Table'[Technology] = "T1"
        || 'Table'[Technology] = "T3"
        || 'Table'[Technology] = "T4"
        || 'Table'[Technology] = "T5"
)

在表 1 中，我使用 LOOKUPVALUE 将表 2 中的测试总数相加并计算了通过百分比".

In Table1, I added the total number of tests from Table2 using LOOKUPVALUE and calculated the ‘%Passed’.

当我想使用 IF 语句获得100% 通过"标志(是/否)时:

When I want to get the ‘100% Passed’ flag (Yes/No) using IF statement:

100% Passed = IF(%'Table Test1'[%Passed]=1,"Yes","No")

我收到此错误消息:

解析时出现以下语法错误:invalid token, Line1, Offset 4, %

是因为%Passed"是计算字段吗?你知道解决办法吗?我已经为此工作了好几天，我感到很沮丧.有没有更有效的方法来获得这个结果?

Is it because the ‘%Passed’ is a calculated field? Do you know a way around? I’ve been working on this for days and I’m getting frustrated. Is there a more efficient way to get this result?

提前感谢您的帮助.

推荐答案

我个人更喜欢 SUMMARIZE 而不是 GROUPBY 虽然它们非常相似.您可以使用 ADDCOLUMNS 添加附加逻辑，这些逻辑引用您在汇总中计算的列，以获得 100% Passed 列.

I personally prefer SUMMARIZE over GROUPBY although they are quite similar. You can use ADDCOLUMNS to add additional logic that refers to columns you calculated in your summarization to get the 100% Passed column.

Summary =
CALCULATETABLE (
    ADDCOLUMNS (
        SUMMARIZE (
            'Table',
            'Table'[IP],
            "% Passed", DIVIDE (
                CALCULATE ( COUNTROWS ( 'Table' ), 'Table'[Status] = "Passed" ),
                COUNTROWS ( 'Table' )
            )
        ),
        "100% Passed", IF ( [% Passed] = 1, "Yes", "No" )
    ),
    NOT ( 'Table'[Technology] IN { "T2", "T6" } )
)

这篇关于Power Bi/Dax:使用过滤器汇总表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！