我正在学习 DAX,但对 PowerBI 中的 RANKX() 感到困惑.这是我的数据:
I am learning DAX and confused about the RANKX() in PowerBI. Here is my data:
这是我的衡量标准:
Rank = RANKX( ALL(RankDemo[Sub Category]), CALCULATE(SUM(RankDemo[My Value])))这是我的视觉效果:
RANKX() 工作正常,但字段 [My Value] 必须在 PowerBI 字段设置中求和:
The RANKX() works fine, but the field [My Value] has to be summed in the PowerBI field setting:
如果我选择Don't Summarize,排名将全部为1.有人可以解释一下吗?Sum 与 DAX 中的 RANKX() 或 CALCULATE() 有什么关系.谢谢.
If I choose Don't Summarize, the rank will be all 1. Anyone could explain this? What does the Sum have to do with the RANKX() or CALCULATE() in DAX. Thanks.
推荐答案您遇到的问题与RANKX无关.问题在于所谓的隐式测量"——Power BI 和 Power Pivot 中(不幸的是)一种常见的不良做法.
The problem you are experiencing has nothing to do with RANKX. The issue is with what's called "Implicit measure" - an (unfortunately) common bad practice in Power BI and Power Pivot.
Power BI 中的数字字段可以充当 2 个角色:
Numeric fields in Power BI can serve 2 roles:
- 它们可以输入到 DAX 度量中(例如 SUM() 等)
- 或者它们可以是过滤器(即,提供与视觉对象中的子类别"相同的功能).
当您将我的值"放入表中而不进行任何计算(不汇总")时,您是在告诉 Power BI 您希望我的值"用作筛选器.在 Excel 数据透视表中,它会相当于将我的价值"放入行"区域而不是价值".所以表中的每一行现在都按子类别+我的价值"分组,而不仅仅是子类别"(换句话说,你已经我的价值"是过滤器上下文的一部分).由于子类别 + 我的价值"的每个组合都是唯一的,因此您实际上是在对包含 1 条记录的表进行排名(这就是它总是返回 1 的原因).
When you drop 'My Value" into a table without any calculation ("Don't summarize"), you are telling Power BI that you want "My Value" to serve as a filter. In Excel pivot tables, it would be equivalent to dropping "My Value" into "Rows" area instead of "Values". So each row in your table is now grouped by "Subcategory + My Value", not just "Subcategory" (in other words, you've made "My Value" a part of your filter context). Since each combination of "Subcategory + My Value" is unique, you are essentially ranking tables consisting of 1 record (that's why it always returns 1).
当您为我的值"选择SUM"时,它不再是一个行过滤器,而是一个度量.因此,您现在过滤的上下文不是子类别"+我的值",而只是子类别",并且您的 RANKX 公式可以正常工作.您可以通过从表中删除总和的我的价值"轻松地看到这一点 - RANKX 度量仍然以相同的方式工作.
When you choose "SUM" for "My Value", it's no longer a row filter - it's now a measure. So you filter context now is not "Subcategory" + "My Value", but just "Subcategory", and your RANKX formula works properly. You can easily see this by removing summed "My Value" from the table - RANKX measure will still work the same way.
当您对我的值"使用此SUM"聚合时,您是在告诉 Power BI 为您隐式创建 DAX 度量(这就是它被称为隐式度量"的原因).每当您将数字字段直接放入视觉对象时,都会发生这种情况.由于多种原因,这种隐含措施在经验丰富的设计师中被认为是一种不好的做法,例如:
When you use this "SUM" aggregation for "My Value", you are telling Power BI to create a DAX measure for you implicitly (that's why it's called "implicit measure"). It happens whenever you drop a numeric field into a visual directly. Such implicit measures are considered a bad practice among experienced designers, for a number of reasons, for example:
- 令人困惑(您对 RANKX 的困扰就是一个典型例子);
- 您不能重复使用隐式度量(不能在其他 DAX 度量中引用它们).
解决办法是:
- 永远不要将数字字段直接放入视觉对象中.
- 相反,请始终编写 DAX 度量,然后将其放入视觉对象中.
在您的示例中,我将创建一个明确的 DAX 度量:
In your example, I would create an explicit DAX measure:
Total Value = SUM(RankDemo[My Value])现在您可以在模型中的任何地方使用它.您可以将其放入视觉对象中以查看我的价值"的总和.或者您可以在您的 RANKX 度量中使用它:
Now you can use it everywhere in your model. You can drop it into a visual to see sum of My Value. Or you can use it in your RANKX measure:
Rank = RANKX( ALL(RankDemo[Sub Category]), [Total Value])这种设计的好处是:
- 没有隐藏效果(您确切知道 [Total Value] 的作用)
- 您可以在许多其他公式中使用[总值],而无需一次又一次地编写求和.
- 如果您更改 [总值] 中的 DAX(例如,添加四舍五入),它将自动更新使用它的所有其他公式.
- 重复使用 DAX 度量使公式更简洁、更易于理解.
更多推荐
DAX、PowerBI 中的 RANKX() 问题
发布评论