PowerBI中Zoom参会关联数据的参会人数统计度量值问题

基于组织内Zoom会议数据制作PowerBI报表，组织表通过桥接表「Email to MMID Link」与Zoom出勤事实表建立两个非活动关系（支持员工作为主持人或参会人，且员工可拥有多个邮箱）。使用计算组让用户动态切换查看员工/部门的主持、参会或全部参与的会议指标，会议计数、时长等度量正常，但参会人数统计存在问题：当选择「所选用户为参会人」时，仅统计该员工自身的出勤行，无法统计其参与会议的所有参会者。

需要创建一个度量值，统计Zoom出勤表中所有与所选员工关联的UUID对应的行（无需指定使用主持人或参会人关系）。

已尝试的DAX代码 #

Attendees in Zoom Meetings = 
VAR uuids = ADDCOLUMNS('Zoom Meetings', "validuuid", 'Zoom Meetings'[UUID])
VAR filtered = FILTER('Zoom Attendance', 'Zoom Attendance'[UUID] IN uuids)
RETURN
COUNTROWS(filtered)

现有计算组DAX代码 #

Zoom Hosts = CALCULATE (
    SELECTEDMEASURE (),
    USERELATIONSHIP ( 'Zoom Attendance'[HostEmail], 'Email to MMID Link'[Primary Email] )
)
Zoom Attendees = CALCULATE (
    SELECTEDMEASURE (),
    USERELATIONSHIP ( 'Zoom Attendance'[AttendeeEmail], 'Email to MMID Link'[Primary Email] )
)
All Zoom Participation = VAR att =
    CALCULATE (
        SELECTEDMEASURE (),
        USERELATIONSHIP ( 'Zoom Attendance'[AttendeeEmail], 'Email to MMID Link'[Primary Email] )
    )
VAR host =
    CALCULATE (
        SELECTEDMEASURE (),
        USERELATIONSHIP ( 'Zoom Attendance'[HostEmail], 'Email to MMID Link'[Primary Email] )
    )
RETURN
    att + host

解决方案 #

原DAX未筛选出与所选员工关联的会议UUID，正确思路是先获取员工参与过的所有会议UUID（无论主持还是参会），再统计这些UUID对应的全部出勤行。

基础版度量值 #

Attendees in Zoom Meetings = 
// 获取所选员工的所有关联邮箱
VAR selectedEmails = VALUES('Email to MMID Link'[Primary Email])
// 筛选员工参与过的所有会议UUID
VAR relatedUUIDs = 
    CALCULATETABLE(
        VALUES('Zoom Attendance'[UUID]),
        OR(
            'Zoom Attendance'[HostEmail] IN selectedEmails,
            'Zoom Attendance'[AttendeeEmail] IN selectedEmails
        ),
        ALL('Email to MMID Link') // 解除桥接表筛选限制，确保拿到所有相关会议
    )
// 统计这些会议的所有参会行
RETURN
COUNTROWS(
    FILTER(
        ALL('Zoom Attendance'),
        'Zoom Attendance'[UUID] IN relatedUUIDs
    )
)

适配计算组的动态版本 #

如果需要响应计算组的切换逻辑（主持/参会/全部），可以使用以下代码：

Attendees in Zoom Meetings = 
// 判断当前激活的关系类型
VAR activeRelationship = 
    SWITCH(
        TRUE(),
        ISACTIVE('Zoom Attendance'[HostEmail]), "Host",
        ISACTIVE('Zoom Attendance'[AttendeeEmail]), "Attendee",
        "All"
    )
VAR selectedEmails = VALUES('Email to MMID Link'[Primary Email])
// 根据激活关系筛选对应会议UUID
VAR relatedUUIDs = 
    CALCULATETABLE(
        VALUES('Zoom Attendance'[UUID]),
        SWITCH(
            activeRelationship,
            "Host", 'Zoom Attendance'[HostEmail] IN selectedEmails,
            "Attendee", 'Zoom Attendance'[AttendeeEmail] IN selectedEmails,
            OR(
                'Zoom Attendance'[HostEmail] IN selectedEmails,
                'Zoom Attendance'[AttendeeEmail] IN selectedEmails
            )
        ),
        ALL('Email to MMID Link')
    )
RETURN
COUNTROWS(
    FILTER(
        ALL('Zoom Attendance'),
        'Zoom Attendance'[UUID] IN relatedUUIDs
    )
)

关键说明 #

1. selectedEmails解决员工多邮箱关联问题，获取当前筛选下员工的所有绑定邮箱。
2. relatedUUIDs通过CALCULATETABLE筛选目标会议，ALL('Email to MMID Link')避免桥接表的筛选限制，确保拿到所有相关会议。
3. 最终通过筛选出勤表中目标UUID的所有行，得到会议的总参会人数。

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