Power BI, DAX, Many-to-one and relational tables

using these tables with a many to one relationship between Builds and Apps

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

we can write a dax measure that counts the number of apps per build that are not in “Complete” status. Since an app can have just one status, otherwise the many to one relationship would break, it’s enough to filter out status = “Complete” when counting.

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

With this formula we can use a Table Visual to get this result

enter image description here

Edit: this will also handle cases where there are missing Apps in Apps table, just ignoring them

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

CLICK HERE to find out more related problems solutions.

Leave a Comment

Your email address will not be published.

Scroll to Top