Power BI、DAX、多对一和关系表

我之前在这里问过一个问题：DAX 子查询度量？

有关如何为可视化创建特定度量列的说明。为了使示例简单，我将其保留在一个虚构的表中，并且 DAX 查询运行得非常好。

然而，实际上，度量列所针对的可视化是由多个连接表组成的。 DAX 查询的结果出乎意料地全为零！所以我将在这里重构我的示例以获得更多帮助......

要求

我想要统计有多少“应用程序”不等于特定“构建”的“完整”。

数据模型

Builds
Build
App

Apps
App
Status

样本数据

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

关系

这种关系是“许多 Builds.App 到一个 Apps.App”。

可视化表

这是我的可视化 - 请注意不同的表格：

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

这是我需要的结果：

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

尝试1（不工作！）

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

尝试2（不工作！）

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

尝试3（不工作！）

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

使用这些表在构建和应用程序之间建立多对一的关系

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

我们可以编写一个 dax 度量来计算每个构建中不处于“完成”状态的应用程序的数量。由于一个应用只能有一种状态，否则多对一的关系就会被打破，所以在计数时过滤掉 status = "Complete" 就足够了。

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

通过这个公式，我们可以使用表格视觉来获得这个结果

编辑：这也将处理应用程序表中缺少应用程序的情况，只需忽略它们

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