Power BI混合模型中如何同时启用活动与非活动关系？

模型概况 #

所有表基于Microsoft SQL连接，各表核心信息：

• Buffer_Data：事实表，记录客户交互、维护、传感器触发日志，近10亿条记录，采用Direct Query模式。通过拼接Machine ID+MastID关联Mast表，无直接关联Property_Table的链路。
• Mast：Type 2缓慢变化维度表（机器表），含定位机器位置的location字符串，通过location_ID/Location_Code关联Location_Filters；需关联AuditDate表确定指定日期的活跃版本，数百万条记录，Direct Query模式。
• AuditDate：记录每台机器location每日的活跃版本号，因配置变更产生多版本，数亿条记录，Direct Query模式。
• Location_Filters：导入表，含可关联物业的唯一location列表，数万行；Property_Table：导入表，20多行；Date表：导入表，仅含datetime列，与Buffer_Data的datetime列建立非活动关系。

核心需求 #

同时启用Date表与Buffer_Data的活动/非活动日期关系：一方面通过AuditDate过滤Mast表（至少筛选出10条记录）提升查询效率，另一方面通过Date表限制时间范围。

已尝试方案及问题 #

1. 移除AuditDate表：结果符合预期，但查询速度极慢
2. 取消Direct Query：通过Excel+M代码+SQL限制导入数据，无法发布且多人使用易冲突
3. 创建Buffer Data的Reference Table：未实现预期过滤效果
4. 新增第二个日期表：查询仍慢，效果不佳
5. 在CALCULATE中使用两个USERELATIONSHIPS：出现锁定冲突

可行解决方案 #

1. 拆分CALCULATE逻辑避免关系冲突 #

不要在单个CALCULATE中同时启用两个关系，通过嵌套CALCULATE分离过滤逻辑：先通过AuditDate筛选Mast的活跃版本，再启用Date与Buffer_Data的非活动关系做时间范围限制。示例DAX：

目标度量值 = 
VAR 活跃机器集合 = 
    CALCULATETABLE(
        Mast,
        USERELATIONSHIP(AuditDate[Date], 'Date'[Date]) -- 关联日期与AuditDate，筛选对应日期的活跃机器
    )
RETURN
CALCULATE(
    [基础度量值], -- 替换为你的实际业务度量值
    USERELATIONSHIP('Date'[Date], Buffer_Data[Datetime]), -- 启用Date与Buffer_Data的非活动关系
    Buffer_Data[拼接字段] IN SELECTCOLUMNS(活跃机器集合, "关联字段", Mast[拼接字段]) -- 用筛选后的活跃机器过滤Buffer_Data
)

注：这里的「拼接字段」指Machine ID+MastID的组合字段，可提前在Mast表中计算生成，避免DAX层面重复拼接。

2. SQL端预创建关联视图（推荐） #

针对Direct Query场景，在SQL服务器上创建视图，预先完成Buffer_Data与活跃Mast版本的关联，减少Power BI端DAX的计算压力。示例SQL视图：

CREATE VIEW vw_Buffer_With_Active_Mast
AS
SELECT 
    bd.*,
    m.*
FROM Buffer_Data bd
INNER JOIN Mast m 
    ON CONCAT(bd.MachineID, bd.MastID) = CONCAT(m.MachineID, m.MastID)
INNER JOIN (
    -- 先获取每个location每日的最新版本
    SELECT Location, Date, MAX(Version) AS LatestVersion
    FROM AuditDate
    GROUP BY Location, Date
) ad_latest
    ON m.Location = ad_latest.Location 
    AND CAST(bd.Datetime AS DATE) = ad_latest.Date
    AND m.Version = ad_latest.LatestVersion

在Power BI中直接导入该视图作为事实表，直接将Date表与视图的Datetime列建立活动关系即可，无需再处理AuditDate的关联逻辑，大幅提升查询效率。

3. 用DAX筛选器替代关系启用 #

如果USERELATIONSHIP始终存在锁定问题，直接用FILTER和EXISTS函数实现双维度过滤，完全绕开关系配置：

筛选后度量值 = 
VAR 选定日期范围 = VALUES('Date'[Date])
RETURN
CALCULATE(
    COUNT(Buffer_Data[记录ID]), -- 替换为实际度量值
    -- 过滤Buffer_Data的时间范围
    FILTER(
        Buffer_Data,
        CAST(Buffer_Data[Datetime] AS DATE) IN 选定日期范围
    ),
    -- 过滤对应日期的活跃机器
    FILTER(
        Mast,
        EXISTS(
            AuditDate,
            AuditDate[Location] = Mast[Location]
            AND AuditDate[Date] = CAST(Buffer_Data[Datetime] AS DATE)
            AND AuditDate[Version] = Mast[Version]
        )
    )
)

4. 优化SQL端索引 #

针对Direct Query的瓶颈字段创建索引，加快关联与筛选速度：

• 给AuditDate表创建Location + Date的组合非聚集索引，包含Version字段：

CREATE NONCLUSTERED INDEX IX_AuditDate_Location_Date 
ON AuditDate(Location, Date)
INCLUDE(Version)

• 给Mast表创建MachineID + MastID的组合索引，包含Location和Version字段：

CREATE NONCLUSTERED INDEX IX_Mast_MachineMastID 
ON Mast(MachineID, MastID)
INCLUDE(Location, Version)

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