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

Question

I previously asked a question here: DAX subquery measure?

for instruction on how to create a specific measure column for a visualisation. To keep the example simple, I kept it to one fictitious table and the DAX query worked really well.

In reality, however, the visualisation that the measure column is for is made up of multiple joined tables. And the results of the DAX query unexpectedly produced all zeros! So I'll refactor my example here for more help...

Requirement

I want a count of how many 'Apps' are not equal to 'Complete' for a specific 'Build'.

Data Model

Builds
Build
App

Apps
App
Status

Sample Data

Builds

Build...........App
Build1..........App1
Build1..........App2
Build1..........App9
Build2..........App3
Build3..........App1
Build3..........App5
Build3..........App8
Build3..........App9

Apps

App...........Status
App1..........UAT
App2..........Complete
App9..........New
App3..........Complete
App5..........UAT
App8..........Complete

Relationship

The relationship is MANY Builds.App to ONE Apps.App.

Visualisation Table

This is my visualisation - note the different tables:

Builds.Build....Builds.App....Apps.Status
Build1..........App1..........UAT
Build1..........App2..........Complete
Build1..........App9..........New
Build2..........App3..........Complete
Build3..........App1..........UAT
Build3..........App5..........UAT
Build3..........App8..........Complete
Build3..........App9..........New

This is my required results:

Builds.Build....Builds.App....Apps.Status....AppsNotCompleteForBuild
Build1..........App1..........UAT............2
Build1..........App2..........Complete.......2
Build1..........App9..........New............2
Build2..........App3..........Complete.......0
Build3..........App1..........UAT............3
Build3..........App5..........UAT............3
Build3..........App8..........Complete.......3
Build3..........App9..........New............3

ATTEMPT 1 (Not working!)

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

ATTEMPT 2 (Not working!)

Measure 5 = CALCULATE (
    COUNT ( Builds[App] ),
    FILTER (
        ALL ( Builds[Build] ),
        Builds[Build] = SELECTEDVALUE ( Builds[Build] )            
    ),
    FILTER (RELATEDTABLE(Apps),
    Apps[Status] <> "Complete")
) + 0

ATTEMPT 3 (Not working!)

Measure5 = CALCULATE (
   COUNTAX(FILTER( Builds 
            ,  RELATED(Apps[Status]) <>"Complete"  
               && Builds[Build] = SELECTEDVALUE(Builds[Build])
             )  
     ,Builds[App])
) + 0

sergiom sergiom · Accepted Answer · 2020-11-08T11:46:47

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

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

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

1 Answers