PowerBI跨团队可视化：员工筛选功能异常求助

需求与问题 #

• 任务：创建可视化展示多团队参与的项目并区分各团队，使用100%堆积条形图
• 可用表：Projects、Employees、EmployeeBookings、Teams、Calendar
• 筛选需求：支持按团队名称、员工姓名、财年筛选，且支持多切片器多选
• 当前问题：团队和财年筛选正常，但员工筛选不符合预期——选择John John时，仅显示他个人参与的项目，实际需要展示其所在团队参与的所有跨团队项目

当前筛选效果 #

按团队筛选的效果 #

按员工姓名筛选的结果 #

当前使用的度量值 #

Count Cross Team 6 = 
VAR SelectedTeams = VALUES ( Teams[Name] )
VAR SelectedEmployees = VALUES ( Employees[Name] )
VAR SelectedTeamsAndEmployees = UNION ( SelectedTeams, SelectedEmployees )

VAR a =
    SUMMARIZE (
        ADDCOLUMNS (
            ALLSELECTED ( EmployeeBookings ),
            "Projects", RELATED ( Projects[Name] ),
            "Team", RELATED ( Employees[Team] ),
            "Employee", RELATED ( Employees[Name] )
        ),
        [ID Project],
        [Projects],
        [Team],
        [Employee]
    )

VAR b =
    COUNTROWS ( FILTER ( a, [Projects] IN VALUES ( Projects[Name] ) ) )

RETURN
    IF (
        ISFILTERED ( Teams[Name] ) && COUNTROWS ( SelectedTeamsAndEmployees ) > 0,
        IF (
            b > 1,
            COUNTROWS (
                FILTER (
                    a,
                    [Projects]
                        IN VALUES ( Projects[Name] )
                            && [Team] IN SelectedTeamsAndEmployees
                )
            )
        ),
        IF (
            ISFILTERED ( Employees[Name] ) && COUNTROWS ( SelectedTeamsAndEmployees ) > 0,
            COUNTROWS (
                FILTER (
                    a,
                    [Projects]
                        IN VALUES ( Projects[Name] )
                            && [Employee] IN SelectedTeamsAndEmployees
                )
            ),
            IF (
                b > 1,
                COUNTROWS (
                    FILTER (
                        a,
                        [Projects]
                            IN VALUES ( Projects[Name] )
                                && [Team] IN VALUES ( Employees[Team] )
                    )
                )
            )
        )
    )

数据模型 #

相关资源 #

pbix文件下载

已尝试修改度量值、创建关联表测试，但未解决问题，恳请协助。

内容的提问来源于stack exchange，提问作者Konrad90