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中的测试总数，并计算了％Passed".

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")

我收到此错误消息:

在解析过程中发生以下语法错误:无效令牌，第1行，偏移量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屋！