我有一个事实数据表,其中包含settlement_date、product_id、service_id、location_id、ticket_id和srv_adjusted_earning列。
我已确定 DAX 查询以生成一个计算列,该列对日期范围内的srv_adjusted_earning列求和:结算日期和结算日期 - 27 天(即 4 周窗口(:
=CALCULATE(
SUM(factService[SRV_ADJUSTED_EARNING]),
DATESBETWEEN
(
factService[SETTLEMENT_DATE],
DATEADD(factService[SETTLEMENT_DATE], -27, DAY),
factService[SETTLEMENT_DATE]
),
FILTER(factService, factService[PRO_ID] = EARLIER(factService[PRO_ID])),
FILTER(factService, factService[SER_ID] = EARLIER(factService[SER_ID])),
FILTER(factService, factService[LOC_ID_SELLING] =
EARLIER(factService[LOC_ID_SELLING])),
FILTER(factService, factService[TIS_ID] = EARLIER(factService[TIS_ID]))
)
我正在尝试将此 DAX 计算列转换为度量值,并尝试了以下方法:
blob:=CALCULATE
(
SUM(factService[SRV_ADJUSTED_EARNING]),
DATESBETWEEN
(
factService[SETTLEMENT_DATE],
DATEADD(factService[SETTLEMENT_DATE], -27, DAY),
factService[SETTLEMENT_DATE]
),
ALLEXCEPT(factService, factService[PRO_ID]),
ALLEXCEPT(factService, factService[SER_ID]),
ALLEXCEPT(factService, factService[LOC_ID_SELLING]),
ALLEXCEPT(factService, factService[TIS_ID])
)
但我得到:错误:度量值"factService"[blob] 中的计算错误:无法确定表"factService"中列"SETTLEMENT_DATE"的单个值。当度量值公式引用包含许多值的列而未指定聚合(如最小值、最大值、计数或总和(以获取单个结果时,可能会发生这种情况。
有人知道我如何解决这个问题吗?
正如错误所提到的,问题出在 factService[SETTLEMENT_DATE]
.在度量值中,没有行上下文,因此它知道您正在谈论的日期,因此您需要以某种方式指定它。我建议按照以下行使用变量:
blob :=
VAR SettleDate = MAX ( factService[SETTLEMENT_DATE] )
RETURN
CALCULATE (
SUM ( factService[SRV_ADJUSTED_EARNING] ),
DATESBETWEEN (
factService[SETTLEMENT_DATE],
SettleDate - 27,
SettleDate
),
ALLEXCEPT (
factService,
factService[PRO_ID],
factService[SER_ID],
factService[LOC_ID_SELLING],
factService[TIS_ID]
)
)
在这里,变量在当前筛选器上下文中选择最大结算日期。如果这不是您所需要的,请相应地调整定义。