DAX and DISTINCT COUNTROWS

Question

Based on this previous question: Power BI, DAX, Many-to-one and relational tables

And using this data:

Builds = 
DATATABLE(
    "Build", STRING,
    "App", STRING,
    {
        { "Build1", "App1" },
        { "Build1", "App2" },
        { "Build1", "App9" },
        { "Build2", "App3" },
        { "Build3", "App1" },
        { "Build3", "App5" },
        { "Build3", "App8" },
        { "Build3", "App9" }
    }
)

Apps = 
DATATABLE(
    "App", STRING,
    "Status", STRING,
    {
        { "App1", "UAT" },
        { "App2", "Complete" },
        { "App9", "New" },
        { "App3", "Complete" },
        { "App5", "UAT" },
        { "App8", "Complete" }
    }
)

I could use this DAX Measure to tell me how many 'App' for a 'Build' were not 'Complete':

# not complete = 
IF(
    HASONEVALUE( Builds[Build] ),
    VAR CurrentBuild =
        SELECTEDVALUE( Builds[Build] )
    VAR CurrentApp =
        SELECTEDVALUE( Apps[App] )
    VAR Result =
        COUNTROWS(
            FILTER(
                ALLNOBLANKROW( Builds ),
                Builds[Build] = CurrentBuild
                    && RELATED( Apps[Status] ) <> "Complete"
                    && NOT ISBLANK( RELATED( Apps[Status] ) )
            )
        ) + 0
    RETURN
        IF( NOT ISBLANK( SELECTEDVALUE( Apps[Status] ) ), Result )
)

However, if I've later discovered that if an App appears on a build more than once, it returns an incorrect result. For example:

 Builds = 
    DATATABLE(
        "Build", STRING,
        "App", STRING,
        {
            { "Build1", "App1" },
            { "Build1", "App1" },
            { "Build1", "App1" },
            { "Build1", "App2" },
            { "Build1", "App9" },
            { "Build2", "App3" },
            { "Build3", "App1" },
            { "Build3", "App5" },
            { "Build3", "App8" },
            { "Build3", "App9" }
        }
    )

Note the duplicate:

 { "Build1", "App1" },

My existing DAX query would count this App (in UAT) as being 3 apps 'not Complete', when in actual fact it is only one app. I tried this:

# not complete = IF(
    HASONEVALUE( Builds[Build] ),
    VAR CurrentAssetName =
        SELECTEDVALUE( Builds[Build] )
    VAR CurrentApp =
        SELECTEDVALUE( Builds[Software])
       
    VAR Result = CALCULATE(
        COUNTROWS(
           DISTINCT(Builds[App])
        ),
        FILTER(ALLNOBLANKROW( Builds ),
        Builds[Build] = CurrentAssetName        
                && RELATED( Apps[Status] ) <> "Complete"
        && NOT ISBLANK( RELATED( Apps[Status] ) )
        )
        ) + 0
    RETURN Result
)

But the CALCULATE function doesn't give me a result for every row, and I require a result for every row in the table.

Can anybody assist?

Thank you!

Alexis Olson Alexis Olson · Accepted Answer · 2020-11-12T20:00:49

I think you may be making it more complicated than necessary.

With this measure

IncompleteApps = 
COUNTROWS (
    FILTER (
        ALL ( Apps ),
        Apps[App] IN VALUES ( Builds[App] )
            && Apps[Status] <> "Complete"
    )
) + 0

you get the following:

If you add Build[App] as another level:

If these are not the results you're expecting, then you need to specify in your question what your output should look like.

DAX and DISTINCT COUNTROWS

1 Answers