英文:
Not sure of why count is different when using these 2 measures I created
问题
我有一个切片器，可以选择日期范围的结束日期。
然后我有一个名为"Date Selected"的度量，它只获取切片器上选择的日期
Date Selected = max(MachineData[StartTime])
然后我有两个不同的度量
Test1 = COUNTROWS(FILTER(MachineData,
MachineData[StartTime] >= Max(MachineData[StartTime]) - 31 ))
Test2 = COUNTROWS(FILTER(MachineData,
MachineData[StartTime] >= MachineData[Date Selected] - 31))
Test1给我一个结果是3800，而Test2给我一个结果是18k
为什么它们给我不同的结果？难道Max(MachineData[StartTime])不应该和MachineData[Date Selected]一样吗？
英文:
I've got a slicer where I can select the end date of a date range.
Then I got a measure called "Date Selected" that just obtains the date selected on the slicer
Date Selected = max(MachineData[StartTime])
Then I got 2 different measures
Test1 = COUNTROWS(FILTER(MachineData,
MachineData[StartTime] >= Max(MachineData[StartTime]) - 31 ))
Test2 = COUNTROWS(FILTER(MachineData,
MachineData[StartTime] >= MachineData[Date Selected] - 31))
Test1 gives me a result of 3800 and Test2 gives me a result of 18k
Why are they fiving me different results? isn't Max(MachineData[StartTime]) supposed to be the same as MachineData[Date Selected] ?
答案1
得分: 2
这是与第一个度量中的上下文转换和缺乏相关的情况有关的。
Test1 度量与 Test2 不等同，这是因为在一个度量内部使用另一个度量时，实际上使用了隐式的 CALCULATE 函数。
要使这两个度量等同，你需要像这样编写第一个度量：
Test1 = COUNTROWS(FILTER(MachineData, MachineData[StartTime] >= CALCULATE(Max(MachineData[StartTime])) - 31 ))
你的 Test1 度量输出不同结果的原因是 MAX(MachineData[StartTime]) 在查询上下文中计算，而不是在过滤表的行上下文中计算。CALCULATE 引入了行上下文，这意味着最大日期计算会针对 MachineData 表的每一行发生，从而使过滤条件对表中的每一行都成立（实际上，你将 FILTER 函数中的条件缩减为 0 >= -31）。
要在 Test2 度量中实现你想要的效果，请尝试以下方式：
Test2 =
VAR selDate = [Date Selected]*
RETURN
COUNTROWS(FILTER(MachineData, MachineData[StartTime] >= selDate - 31))
请阅读有关上下文转换的更多信息 这里 (sqlbi)。
*顺便提一下 - 不建议对度量使用表限定符。
英文:
Ah, this is do to context transition and lack thereof in the first measure.
The Test1 measure is not equivalent to Test2 and this is due to the fact that when using a measure within another measure, you are actually using an implicit CALCULATE function.
For the two measures to be equivalent, you'd need to write the first one like this:
Test1 = COUNTROWS(FILTER(MachineData,
MachineData[StartTime] >= CALCULATE(Max(MachineData[StartTime])) - 31 ))
The reason why your Test1 measure outputs different results is because MAX(MachineData[StartTime]) is calculated at the query context, not at the row context of the filtered table. CALCULATE introduces row context, which means that the max date calculation occurs for every row of the MachineData table, which in turn makes the filter condition true for each row in the table (you actually reduce the condition in the FILTER function to 0 >= -31).
Try this instead to achieve what you want within the Test2 measure:
Test2 =
VAR selDate = [Date Selected]*
RETURN
COUNTROWS(FILTER(MachineData,
MachineData[StartTime] >= selDate - 31))
Please read more on context transition here (sqlbi)
*As a side note - it is not advised to use table qualifiers for measures
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论