Power BI，DAX，多对一和关系表 [英] Power BI, DAX, Many-to-one and relational tables

问题描述

我以前在这里问过一个问题:

这还会处理应用程序"表中缺少应用程序"的情况，只需忽略它们

 #未完成=如果(HASONEVALUE(Builds [Build])，VAR CurrentBuild =SELECTEDVALUE(Builds [Build])VAR CurrentApp =SELECTEDVALUE(Apps [App])VAR结果=COUNTROWS(筛选(ALLNOBLANKROW(构建)，Builds [Build] = CurrentBuild&&相关的(Apps [Status])<>完成"&&不是ISBLANK(相关(Apps [Status]))))+ 0返回IF(NOT ISBLANK(SELECTEDVALUE(Apps [Status]))，结果)) 

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

解决方案

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，多对一和关系表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！