DAX子查询措施? [英] DAX subquery measure?

问题描述

我在DAX查询方面苦苦挣扎，想知道您能否提供帮助?

I'm struggling with a DAX query and wondered if you could help?

考虑此表(或可视化表)，称为内部版本":

Consider this table (or visualisation) called 'Builds':

Build....App....Status
Build1...App1...UAT
Build1...App2...Complete
Build2...App1...Complete
Build2...App2...Complete

我想添加一个名为"AppsOutstanding"的度量列，以显示该版本中不是"Complete"的应用数量.像这样:

I would like to add a measure column called 'AppsOutstanding' to show a count of Apps for that Build that aren't 'Complete'. Like so:

Build....App....Status......AppsOutstanding
Build1...App1...UAT.........1
Build1...App2...Complete....1
Build2...App1...Complete....0
Build2...App2...Complete....0

我几乎需要做一个子查询"措施！像这样:

I almost need to do a 'subquery' measure!? Something like:

SELECT COUNT(Status) FROM Builds
WHERE Build = [The Build In This Row]
AND Status <> 'Complete'

我有点困惑如何将其转换为DAX?这是我失败的尝试:

I'm a bit stumped how to translate this into DAX? Here is my unsuccessful attempt:

AppsUnavailable = CALCULATE (
    count(Builds[Build]),
    CALCULATETABLE (
        SUMMARIZE ( Builds,Builds[Status] ),
        Builds[Status] <> "Complete"
    ))

提前谢谢！

更新

我已经尝试过此操作，但计数不起作用，而且该DAX过滤器还过滤"了完成"状态超出我的实际结果！我不想要的.我只想过滤完成"状态超出了我的衡量标准....

I've tried this, but the count isn't working, and also this DAX filters "Complete" statuses out of my actual results! Which i don't want. I only want to filter the "Complete" statuses out of my count measure....

AppsUnavailable =
CALCULATE (
    COUNT ( Builds[Build] ),
    FILTER (
        ALL ( Builds[Build] ),
        Builds[Build] = SELECTEDVALUE ( Builds[Build] )
    ),
    FILTER (
        ALL ( Builds[Status] ),
        Builds[Status] <> "Complete"
    )
)

更新2

我认为我做的事情根本上是错误的.我真的很愚蠢，只是找到其他同名的"Build"，而它仍然只返回1！

I think I'm doing something fundamentally wrong. I've really dumbed it down to just find other 'Builds' with the same name, and it still only returns 1's!

AppsUnavailable =
CALCULATE (
    COUNT ( Builds[Build] ),
    FILTER (
        ALL ( Builds[Build] ),
        Builds[Build] = SELECTEDVALUE ( Builds[Build] )
    )
)

更新3

此查询(在单个表(无联接)上进行测试时)生成以下内容:

This query (when testing on a single table (no joins) sample) produces this:

Build....App....Status......AppsOutstanding
Build1...App1...UAT.........1
Build1...App2...Complete....0
Build1...App2...UAT.........1
Build2...App1...Complete....0
Build2...App2...Complete....0

但是我实际上需要这个:

But i actually need this:

Build....App....Status......AppsOutstanding
Build1...App1...UAT.........2
Build1...App2...Complete....0
Build1...App2...UAT.........2
Build2...App1...Complete....0
Build2...App2...Complete....0

因此Build1有2个未完成的应用程序.

So Build1 has 2 Apps that are not complete.

然后，我需要研究为什么在实时"环境中获得全1的原因.一定要对2个表进行过滤，而不是1 ....

I then need to look into why I'm getting all 1's in the 'live' environment. It must be to do with filtering on 2 tables as opposed to 1....

推荐答案

您可以用SELECTEDVALUE(Builds [Build])替换[此行中的构建].试试这个:

You can replace [The Build In This Row] with SELECTEDVALUE ( Builds[Build] ). Try this:

AppsUnavailable =
CALCULATE (
    COUNT ( Builds[App] ),
    FILTER (
        ALL ( Builds[Status], Builds[Build] ),
        Builds[Build] = SELECTEDVALUE ( Builds[Build] )
            && Builds[Status] <> "Complete"
    )
) + 0

powerBI表可视化内部使用SUMMARIZECOLUMNS，该行已删除，而没有来自度量的数据.您有2个选择:-右键点击GroupBy列->显示没有数据的项目"；-在最后一个括号之后将+0添加到计算中

powerBI Table visualization internal use SUMMARIZECOLUMNS which is removed row without data from the measure. You have 2 options: -Right click on GroupBy Column -> "Show item with no data" -Add +0 to calculation after the last parenthesis

更新:

CALCULATE (
    COUNT ( YourTable[App] ),
    FILTER (
        ALL ( YourTable[App], YourTable[Build], YourTable[Status] ),
        YourTable[Build] = SELECTEDVALUE ( YourTable[Build] )
            && YourTable[Status] <> "Complete"
    )
) + 0

这篇关于DAX子查询措施?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！