本文介绍了基于多个列获得排名技术,排名可以是静态的也可以是动态的。
DAX提供RANKX函数,用于根据度量或列计算表的排名。RANKX的一个限制是它只能使用单个表达式进行排名。通常,有必要使用多个列来获得排名,这是因为业务需求决定了排名,或者是因为您想对具有不同条件的关系进行排名。
作为演示,我们根据购买量对客户进行排名。为了人为地引入联系,我们使用“舍入的销售额”度量,该度量将销售额四舍五入到最接近的千分之一。使用“四舍五入销售”,多个客户显示相同的10,000.00。由于它们是纽带,因此必须根据其名称按字母顺序定义其排名。
基于以下两个细节,编写代码有所不同:
计算列比较简单,度量值比较难。如果想要一个静态排名(使用计算列实现),想要一个动态排名(就使用度量值实现)。
用于排名的列的数据类型。在上图中,我们显示了按字母顺序排序,这就是为什么我们使用字符串的原因。如果您在第二个排名列中使用数字,则代码要简单得多。
基于先前的假设,本文显示了同一计算的四个不同版本,并按复杂度递增的顺序进行了介绍。
让我们开始简述下整个算法。我们希望基于两个值对客户进行排名。我们将最相关的值称为HIGH(在我们的示例中为Rounded Sales),而将不太相关的值称为LOW。在所有示例中,HIGH都是相同的,而LOW可以是客户代码或客户名称。客户代码是无意义的数字;它的唯一优点是它是整数,因此更易于处理。客户名称要有用得多,但它带来了一个额外的挑战,即我们需要在公式中使用它之前将其转换为整数。
因为RANKX可以基于单个表达式进行排名,所以我们使用HIGH和LOW的组合,其中HIGH发生了移位,因此最低HIGH高于最高LOW。这可以通过使用HIGH * MAX(LOW)+ LOW作为排名表达式来实现。这样,等于1的HIGH值仍大于最大LOW。如果HIGH的值相等,则排名基于LOW。
对四舍五入的销售和客户代码进行静态排名
如果排名是静态的,则整个实现将依赖于计算出的列。首先,我们创建一个计算列以存储每个客户的舍入销售额的值:
然后,另一个计算列存储该排名。通过首先获取最大代码(LOW),然后将该值用作HIGH(Customer Sales)的乘数来计算排名
值得注意的是,计算列中不需要所有关于Customer的内容。尽管如此,我们更喜欢使用它,使它更明显的排名发生在所有客户。代码的可读性是我们最优先考虑的问题之一。
客户代码对用户不可见,它仅用于创建唯一的排名。因此,我们不必担心哪个客户是第一位的。当我们使用客户名称时,这个小细节将需要额外的工作——因此,我们希望名称较早的客户排名更高。
对四舍五入的销售和客户名称的静态排名
当第二个排名列是一个字符串(例如客户名称)时,不再可以使用简单的乘法运算。您不能将数字乘以字符串。就是说,我们可以使用附加的计算列(按名称计算客户排名)将客户名称转换为数字。
该解决方案使用前面介绍的Customer Sales计算列,然后创建另一个计算列(我们称为Name Ranked),该列按名称计算客户的排名:
另外我们需要注意的一件事是:我们要明确使用DESC进行排名。原因是我们希望客户的名字按字母顺序排在首位,并以较高的整数值排名。通过使用DESC,名为Marco客户的销售额要比名为Alberto客户的销售额高。使用“名称排名”排序后,销售额高的Marco客户排名在Alberto客户后。因此,Alberto客户的名字最终遵循字母顺序排在Marco客户之前:
后一个计算列在结构上与上一个示例中的排名相同。因为它使用“名称排名”列,所以算法是相同的:它对两个数字进行排名。
对舍入后的销售和客户代码进行动态排名
如果需要动态排名,则需要编写代码。度量不能依赖于计算列进行动态计算。因此,不再使用“客户销售计算所得”列。
对度量进行排名是一种有风险的操作:如果在编写代码时不注意小细节,则很容易导致计算速度非常慢。关键是要使用独立于要计算的单个单元格的变量来执行大多数计算。这些变量仅由优化程序评估一次,然后用于评估所有单个单元格:
该操作看起来很复杂,但是其结构相当简单。
首先,它使用REMOVEFILTERS计算MaxCustomerCode来检索整个模型的最大值。通过使用REMOVEFILTERS,结果是一个恒定值,甚至不需要计算查询。
其次,代码创建LookupTable变量。LookupTable创建我们在静态排名示例中使用的Customer Sales计算列的临时副本。稍后在RANKX中使用此临时表,以根据在查找表上计算出的相同表达式对当前客户的HIGH和LOW值进行排名。
对舍入后的销售额和客户名称进行动态排名
最后一个更复杂的示例是按名称在度量中的排名。在最后一个场景中,我们不仅需要为客户销售创建一个计算列;我们还必须为名称排序列创建一个计算列,需要将名称转换为一个数字:
正如您所看到的,尽管这个度量值公式很长,但它只是本文前面概述的技术的组合。
在最后一幅图中,您可以看到按代码排序提供了唯一的值,但排序顺序与用户无关。另一方面,“排名名称”度量同时结合了“按舍入销售额”和“名称”排序。
结论
尽管RANKX不提供按不同列排序的内置功能,但您可以通过将DAX与一些创造性和少量数学运算混合在一起来获得所需的行为。
领取专属 10元无门槛券
私享最新 技术干货