优化DAX代码以解决查询资源超限错误

问题根源 #

你的公式在单项目选择时正常，但全量查看时触发资源超限，核心原因不是变量过多（变量反而能复用计算结果，提升效率），而是过度使用ALL('Date') + FILTER的组合，导致全量计算时重复扫描整个日期表和事实表，资源占用剧增。

优化后的DAX代码 #

// 获取不受日期筛选影响的最新库存日期
VAR max_inventory_date = 
    CALCULATE(
        MAX(fct_fsr_inputs[ReportStartDate]),
        REMOVEFILTERS('Date')
    )

// 获取最新日期的上一个库存日期
VAR prev_inventory_date = 
    CALCULATE(
        MAX(fct_fsr_inputs[ReportStartDate]),
        REMOVEFILTERS('Date'),
        fct_fsr_inputs[ReportStartDate] < max_inventory_date
    )

// 生成高效的日期区间筛选器（上一库存日之后，最新库存日之前）
VAR date_range = DATESBETWEEN('Date'[Date], prev_inventory_date + 1, max_inventory_date - 1)

// 计算交付总量，复用日期区间筛选
VAR gallons_delivered = 
    COALESCE(
        CALCULATE(SUM(fct_invoice_lines[Gallons]), date_range),
        CALCULATE(SUM(fct_st_invoices[Quantity]), date_range)
    )

// 获取期初/期末库存，简化筛选逻辑
VAR ending_inventory = 
    CALCULATE(
        MAX(fct_fsr_inputs[Tank Inventory]),
        'Date'[Date] = max_inventory_date
    )
VAR beginning_inventory = 
    CALCULATE(
        MAX(fct_fsr_inputs[Tank Inventory]),
        'Date'[Date] = prev_inventory_date
    )

VAR days_diff = DATEDIFF(prev_inventory_date, max_inventory_date, DAY)
VAR usage = (beginning_inventory + gallons_delivered) - ending_inventory
VAR actual_rate = usage / days_diff

RETURN actual_rate / 4

关键优化点 #

1. 替换低效日期筛选：用DATESBETWEEN替代FILTER(ALL('Date'))，这是DAX专门优化的日期区间函数，避免手动扫描全表生成筛选器，且筛选器可复用给多个计算。
2. 简化上一库存日期计算：直接在CALCULATE中添加日期条件，配合REMOVEFILTERS清除上下文，不需要扫描整个日期表，性能更优。
3. 用COALESCE简化空值判断：替代IF(ISBLANK)，语义更清晰，性能一致但代码更简洁。
4. 精简库存值筛选：去掉冗余的FILTER(ALL('Date'))，直接通过日期相等筛选，利用上下文传递减少不必要的全表扫描。

额外建议 #

• 确保Date表在Power BI中标记为日期表，DAX引擎会对日期相关函数做额外优化。
• 检查fct_fsr_inputs与Date表的关系是否正确配置，合理利用关系可进一步简化上下文筛选。

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