英文:
DAX TOPN Ascending Returns Blank Values
问题
在Power Pivot中，我有以下DAX度量值，它运行良好，包括与使用CUBEVALUE的切片器连接。
Meas_TopPerformerWeighted:=VAR TopAgentName =
CALCULATE (
FIRSTNONBLANK ([Agent Name], 1 ),
TOPN (
1,
ALL ([Agent Name] ),
[Meas_OverallScoreWeighted], DESC
)
)
VAR TopScore =
CALCULATE (
[Meas_OverallScoreWeighted],
[Agent Name] = TopAgentName
)
RETURN
TopAgentName & ": " & TopScore
但如果我将顺序更改为ASC以查找低分，它可以在整体上正常运行，但不在使用CUBEVALUE函数的切片器时。它对选择的任何日期值返回空白。数据模型的每行上都有数据，没有空白值。对此的任何帮助将不胜感激。
我原本期望它执行计算，然后从该结果中返回底部结果。它会在选择所有值时执行此操作，但在与日期切片器配对时不会使用CUBEVALUE。
英文:
In Power Pivot, I have the following DAX Measure which works great including being connected to a slicer using CUBEVALUE.
Meas_TopPerformerWeighted:=VAR TopAgentName =
CALCULATE (
FIRSTNONBLANK ([Agent Name], 1 ),
TOPN (
1,
ALL ([Agent Name] ),
[Meas_OverallScoreWeighted], DESC
)
)
VAR TopScore =
CALCULATE (
[Meas_OverallScoreWeighted],
[Agent Name] = TopAgentName
)
RETURN
TopAgentName & ": " & TopScore
But if I change the order to ASC to find the low score it works for the overall, but not when I utilize the slicer in the CUBEVALUE function. It returns blank for any date value chosen. And there is data on every row in the data model. No blank values. Any assistance here would be much appreciated.
I was expecting it to perform the calculation and then return the bottom result from that result. It will do it with all values selected, but not when paired with the date slicer usig CUBEVALUE.
答案1
得分: 1
问题很可能是返回了空的成员交集作为底部值。我们可以通过首先按代理人汇总表格来消除这些问题。我用SELECTEDVALUE()替换了firstnonblank()，当存在平局时，它将返回空白或自定义表达式。不过这只是个人偏好。
Meas_TopPerformerWeighted:=
VAR TopAgentName =
CALCULATE (
SELECTEDVALUE ( [AGENT NAME] ),
TOPN (
1,
SUMMARIZE (
table,
[代理人名称],
"Meas_OverallScoreWeighted", [Meas_OverallScoreWeighted]
),
[Meas_OverallScoreWeighted], ASC
)
)
VAR TopScore =
CALCULATE ( [Meas_OverallScoreWeighted], [代理人名称] = TopAgentName )
RETURN
TopAgentName & ": " & TopScore
英文:
The issue is likely that there are empty member intersections being returned as the bottom value. We can eliminate those by summarizing the table by agent first. I replaced the firstnonblank() with SELECTEDVALUE(), which will return blank or a custom expression when there is a tie. This is just preference though.
Meas_TopPerformerWeighted:=
VAR TopAgentName =
CALCULATE (
SELECTEDVALUE ( [AGENT NAME] ),
TOPN (
1,
SUMMARIZE (
table,
[agent Name],
"Meas_OverallScoreWeighted", [Meas_OverallScoreWeighted]
),
[Meas_OverallScoreWeighted], ASC
)
)
VAR TopScore =
CALCULATE ( [Meas_OverallScoreWeighted], [Agent Name] = TopAgentName )
RETURN
TopAgentName & ": " & TopScore
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论