DAX专题12: RANKX() and TOPN()-读书笔记(20)

本章介绍RANKX()和TOPN()函数，这两个函数在解决排名问题上有些相似，但处理过程和技术不一样，我们介绍这两个函数，解决这样两个问题：

🔷 哪些产品最畅销?

🔷 前10名畅销产品的销量是多少?

一、关于 RANKX() 和 TOPN() 

RANKX函数是一个返回标量值(就是一个值)的函数，即在可视化对象中做为一个值呈现出来。TOPN是一个表函数，TOPN返回的表是按特定指标(如总销售额，总利润)比较以后满足条件的多行表(如果指定指标取前1名，且第1名无重复，那就只有一行)。我们知道：返回值的函数可以单独写进度量值，但表不能当作度量值使用或者说不能把一个表作为度量值的唯一参数用。但我们可以将TOPN返回的表作为其他函数的参数使用，现实中通常是作为CALCULATE()的第二参数(当成筛选表使用)。

这样写度量值是错误的：

Invalid Measure 1 = TOPN(10,Products)

Invalid Measure 2 = FILTER(ALL(Products),Products[Category]="Bikes")

这两个公式都返回表，因此像这样使用它们作为度量值是错误的。要让度量值公式生效，公式必须返回单个值。但这并不意味着不能在度量值中使用表函数。接下来的三个度量都包含表函数，但它们都返回值，都是正确的度量值写法:

Valid Measure 1 = RANKX(ALL(Products),[Total Sales])

Valid Measure 2 = CALCULATE([Total Sales], TOPN(10,Products))

Valid Measure 3 = COUNTROWS(FILTER(ALL(Products),Products[Category]= 

"Bikes"))

返回表的函数可以作为其它函数的参数，只要这个参数类型是表就可以。

二、RANKX() 值函数

🔶语法：RANKX(table,expression,[value],[order],[ties])

RANKX前两个参数是必选参数，后三个参数是可选参数，本章只介绍前两个必选参数，后三个参数的用法大家可以自行了解。

尽管RANKX不是表函数，但它的第一个参数必须是一个表，DAX语言中很多函数在使用行上下文时都使用表做为第一参数。也有函数是例外，像UNION函数。

🔶 不用函数如何取得前销售前10名

我们先在报表视图界面插入一个表，并将产品编码[ProductKey]、产品名称[ProductName]和销量[Total Sales]字段放到表的列上，对[Total Sales]进行降序排列。

表中显示所有产品销售为29358677元，我们可以对这个表进行一个筛选，先出销售10名。操作方法：在筛选区域选择按销售额前10名筛选。这个示例我们使用了可视化筛选的功能，这与TOPN函数函数操作方式不一样。

在可视化筛选中，我们可以选择不同的指标放在筛选的值区域做为筛选的标准，这挺有趣的，因为做为筛选值的度量值或列可以不是矩阵中的度量值或列。上个例子中我们是按销量筛选的，我们也可以按毛利润进行筛选前10，如下图

🔶 使用RANKX 获取销售前10名

使用RANKX 我们可以获得每个产品的销售排名，RANKX的计算逻辑是拿一个值和其它多个值比较，确定出这个值的排名(位置)。

第一参数是一个表，比如按产品、按顾客排名，就要用产品表或客户表，计算度量值在某个表的排名时这个表不能被初始筛选或者只能被指定的字段筛选)，这样才能保证是在想要的范围内进行比较排名,我们可以使用ALL函数清除某些字段的筛选或清除表的筛选。

按RANKXA语法我们写个度量值：Product Rank Wrong = RANKX(Products,[Total Sales])

为什么排名都是1呢？NOTE: 分析可视化对象的计算结果是否正确，对于DAX用户来说分析计算的过程是关键，有机会就练练。

计算结果不正确时，首先我们要怀疑是筛选出了问题，可能不是所有的错误都是筛选导致的，从经验来看大部分问题基本上都可以归咎到筛选上。我们来还原一下公式的计算过程，以矩阵行字段第一行为例，RANKX的作用是找出第一行产品名称为：product 312 的总销量，与其它产品名称的销量比较得到一个排名。为了更好理解这个问题，我们作一个测试度量值

Test =  COUNTROWS(Products),把它放到矩阵中

矩阵中TEST度量值是产品表被矩阵行字段(ProductName)筛选的行数，Product表放到矩阵中后矩阵行字段把每个产品名称对应 的产品表的行都筛选出来了。这表明：Product放在函数中做为度量值参数时，表受到矩阵初始筛选的影响，整个度量值也受到了影响，表格被矩阵的行字段都筛选成了一行，所以计算排名时[TOTAL SALES]只看到了一行数据的表，没有其它值跟这个表的总销量比较了，就全部变成了第1名。

这时我们需要ALL函数来帮忙，清除掉对Product的筛选，这样矩阵行字段不能筛选Product表，对于矩阵行字段每个产品的销量都要和整个Product表的产品销售做比较，就能得到正确的排名。

正确写法：Product Rank = RANKX(ALL(Products),[Total Sales])

     Product Rank = RANKX(ALL(Products[ProductName],),[Total Sales])

三、复习一下上下文转换  Filter Context Revisited

上节提到了一个非常重要的观点，需要重申一下并加以扩展对它的认识：函数内部使用的"参数表"会受到来自视觉对象筛选的影响，函数的"参数表"并不是使用整个表,这个表是可视化对象筛选的后的表副本(例如矩阵的行字段筛选后的表)。如果您使用不让可视化对象筛选的整个表，则必须要用ALL函数清除可视化对象对表的筛选，

像Product Rank = RANKX(ALL(Products),[Total Sales])就是清除了外部筛选器(初始筛选器)对产品表的筛选。

度量值和聚合函数也受到来自可视化对象的筛选影响，下图是第16章的一个自定义时间智能公式

第7行上的ALL()函数清除了来自可视化对象(矩阵行)对Calendar的筛选，因此ALL('Calendar')是Calendar表的一个未经筛选的副本。上面的代码中FILTER()函数正在遍历Calendar表(参数1)的一个全部行，第3、9和10行中MAX()每次都不是寻找整个列的最大值，而是通过可视化筛选后的列的最大值。第5行上的[Total Sales]度量值也被可视化对象筛选(当然，该度量值也由CALCULATE()内的筛选函数筛选)。

我们再回来看看上一节的矩阵排名。既然公式已经可以正确运行，并且你也理解了它的工作过程和原理，那么让我们看看筛选在两种场景下的行为。首先，下图显示了在ProductKey而不是[Total Sales]降序排序时发生了什么？

上图的显示方式有别于EXCEL。RANKX函数只对每个产品按销售额的多少进行排名，至于产品以什么顺序排序它并不关心，我们可以做一个切片器和一个卡片图并把刚才的排名度量值放到卡片图中，当你选择某一个产品时，卡片图显示的是这个产品在全部产品中的名次。

这个例子有点意思，列Products[ProductName]都没有在RANKX()公式中使用，但是使用该列做为切片器是有效的。为什么呢?因为我们没有Products[ProductName]使用清除筛选，列Products[ProductName]可以筛选整个Products表。当我们在切片器上选择了一个产品名称时，产品表就只剩下一行，只有一个产品键，列Products[ProductKey]在RANKX()公式中使用了，因此RANKX度量值可以正常运行。总而言之，DAX也不是很复杂，但学好它也确实不是件容易事儿。

四、TOPN()  表函数

与RANKX相比，TOPN函数更直观一些，更容易理解。

语法：

TOPN(<N_Value>, <Table>, <OrderBy_Expression>, [<Order>[, <OrderBy_Expression>, [<Order>]]…]) 

返回值：

一个由 Table 的前 N 行组成的表，或者如果 N_Value 为 0（零）或更小，则为空表。 行不按任何特定顺序排序。

备注：

• 如果在表的第 N 行 order_By 值存在关联，则返回所有关联的行。 因此，在第 N 行有关联时，函数可能返回多于 n 行。

• 如果 N_Value 为 0（零）或更小，则 TOPN 将返回一个空表。

• TOPN 不保证结果的任何排序顺序。

• 可以使用多列做为TOPN的筛选条件，重复第三参数和第四参数。如

Sales from Top 10 Products = 

    CALCULATE ([Total Sales],

        TOPN (10, Sales,Sales[SalesPrice],0,Sales[SalesRemainPrice],0)

    )

• 在已计算的列或行级安全性 (RLS) 规则中使用时，不支持在 DirectQuery 模式下使用此函数。

❇️ 本章向大家展示一下TOPN前两个必选参数的使用，后面几个可选参数大家自己有时间再深入学习吧。

TOPN是一个表函数，这通常用在CALCULATE函数中做为筛选表使用(做为CALCULATE的第二参数)。比如我们想计算销量最好的前10名的产品的销售额时，可以使用TOPN来写个度量值：

Sales from Top 10 Products =  CALCULATE ([Total Sales],

        TOPN (10, Products,[Total Sales]))

放个切片器到报表视图，把Category进来；再做个卡片图，把度量值放进来。当切片器什么也不选时，卡片图得到的数值和我们手工筛选出销售前10的产品的总额相同。

NOTE 1

 TOPN()是一个表函数，因此最常见的用法是作为CALCULATE()函数中的筛选参数。

Sales from Top 10 Products = CALCULATE ([Total Sales],

        TOPN (10, Products,[Total Sales]))

Note 2 

注意，在上面的公式中，这次ALL(Products)没有在TOPN()中使用。这里不需要它，因为卡片图(下图右)中没有筛选条件。这既没有对也没有错;你如何处理它取决于你想做什么，你可以在下面的表格中(下图左)使用这个新的度量值。

前10名销量中，Accessories,Bikes,Clothing各销售了多少一目了然。

因为Products表没有使用ALL函数，度量值要受到视觉对象的筛选影响。在上面的表格中，可以看到按类别划分的前10名中各产品的总销售额。如果在度量中使用ALL(Products)，度量值就忽略来自表格(或其它视觉对象的)任何筛选条件了。

反之，如果在TOPN中使用ALL(Products),那它返回的总是前10个产品的销售总额，把可视化中Products表的筛选统统不考虑。

Sales from Top 10 ALL Products = CALCULATE ([Total Sales],

        TOPN (10, ALL(Products),[Total Sales]))