PowerBI中DAX用USERELATIONSHIP后日期切片器无数据问题

模型中Assessments表与Date表已通过Date completed建立活动关系，现需使用Assessments表的Date commenced字段关联Date表，实现通过日期切片器筛选数据。但编写的DAX度量值拖动切片器时始终显示空白，怀疑RETURN部分存在问题，附上度量值代码及现象截图：

Number of Assessments In Progress = 
VAR SelectedStakeholders = VALUES(Stakeholders[Company Name] )
VAR SelectedSites = 
    SELECTCOLUMNS(
        CALCULATETABLE( 
            Sites, 
            FILTER( 
                Sites,
                Sites[Applicant.lookupValue] IN SelectedStakeholders 
                || Sites[Owner.lookupValue] IN SelectedStakeholders 
                || Sites[Manager.lookupValue] IN SelectedStakeholders
                ||Sites[Tenant(s).lookupValue] IN SelectedStakeholders ||Sites[Contractor.lookupValue] IN SelectedStakeholders 
            )
        ),
        "SiteID", Sites[ID]
    )
RETURN
CALCULATE( 
    DISTINCTCOUNT( Assessments[Assessment Title] ), 
    USERELATIONSHIP(Assessments[Date Commenced],'Date'[Date]),
    DATESBETWEEN(Assessments[Date Commenced],'Measure'[Start Date],'Measure'[EndDate])
)

核心问题点 #

• 定义了SelectedStakeholders和SelectedSites变量，但在RETURN的CALCULATE中完全未使用，导致利益相关方关联的站点筛选逻辑失效。
• DATESBETWEEN直接绑定自定义Measure的日期范围，与日期切片器基于Date表的筛选逻辑冲突，切片器筛选无法传递到Assessments表。
• 未关联Assessments表与选中站点的关系，无法缩小统计范围。

修正后的度量值 #

Number of Assessments In Progress = 
VAR SelectedStakeholders = VALUES(Stakeholders[Company Name])
VAR SelectedSites = 
    SELECTCOLUMNS(
        CALCULATETABLE( 
            Sites, 
            FILTER( 
                Sites,
                Sites[Applicant.lookupValue] IN SelectedStakeholders 
                || Sites[Owner.lookupValue] IN SelectedStakeholders 
                || Sites[Manager.lookupValue] IN SelectedStakeholders
                || Sites[Tenant(s).lookupValue] IN SelectedStakeholders 
                || Sites[Contractor.lookupValue] IN SelectedStakeholders 
            )
        ),
        "SiteID", Sites[ID]
    )
RETURN
CALCULATE( 
    DISTINCTCOUNT(Assessments[Assessment Title]),
    -- 启用Date Commenced与Date表的非活动关系，让切片器筛选生效
    USERELATIONSHIP(Assessments[Date Commenced], 'Date'[Date]),
    -- 应用站点筛选逻辑：仅统计关联选中利益相关方的站点的评估
    Assessments[SiteID] IN SelectedSites,
    -- 可选：添加"In Progress"业务逻辑（已开始但未完成/完成日期在切片器之后）
    ISBLANK(Assessments[Date Completed]) || Assessments[Date Completed] > MAX('Date'[Date])
)

关键修正说明 #

• 新增Assessments[SiteID] IN SelectedSites：将之前定义的站点筛选逻辑应用到统计中，确保只计算关联选中利益相关方的站点数据。
• 移除冲突的DATESBETWEEN：让日期切片器通过USERELATIONSHIP自然筛选Date Commenced在切片器范围内的评估数据。
• 可选业务逻辑补充：如果"In Progress"指已开始但未完成，或完成日期晚于切片器最大日期，加入ISBLANK(Assessments[Date Completed]) || Assessments[Date Completed] > MAX('Date'[Date])条件。

前置检查 #

确保模型中已建立Assessments[Date Commenced]与Date[Date]的非活动关系（因活动关系已绑定Date completed），否则USERELATIONSHIP无法生效。

内容的提问来源于stack exchange，提问作者Chung Man Au