DAX subquery measure?

Question

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

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'

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

Thanks in advance!

UPDATE

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

UPDATE 2

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

UPDATE 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

So Build1 has 2 Apps that are not complete.

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....

msta42a msta42a · Accepted Answer · 2020-11-03T23:00:38

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

UPDATE:

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

DAX subquery measure?

1 Answers