DAX中按列排序的另一种结果
今天，小悦和大家分享一篇国外知名大咖的一篇文章：DAX中“按列排序”的所产生的另一种结果。
Power BI中的“按列排序”功能会导致产生副作用，这些副作用在编写DAX公式时必须了解。本文介绍了这些副作用以及如何编写正确的DAX代码以避免产生错误的结果。
按列排序功能：使用来自另一列的值对列的值进行排序。
例如，报表设计员可能希望按一月，二月和三月的顺序按名称显示月份。因此，按列排序属性应用“Month Name”列，使用“Month Number”列定义月份名称的排序顺序。这样可以避免按字母排序得到你不想要的值。
DAX和MDX中的列排序顺序
MDX中的查询会自动从数据模型继承正确的列排序顺序。MDX查询的结果始终根据“按列排序”设置进行排序。但是，除了基础数据类型的自然排序顺序以外，DAX对列没有任何隐式排序顺序。
因此，DAX查询必须始终在ORDER BY条件中指定排序顺序，这与SQL查询类似。因为DAX要求将ORDER BY中使用的列作为查询结果的一部分，所以对BI列进行排序的Power BI视觉总是会生成一个查询，该查询至少包括两列：报表中请求的列和报表中使用的基础列-按列排序设置。换句话说，按月显示数据的Power BI视觉图必须生成一个包含月名称和月号的查询。例如，参考Power BI中的以下报告：
DAX生成的查询如下：
EVALUATE
TOPN (
502,
SUMMARIZECOLUMNS (
ROLLUPADDISSUBTOTAL (
ROLLUPGROUP (
\’DATE\'[MONTH NAME],
\’DATE\'[MONTH NUMBER]
), “ISGRANDTOTALROWTOTAL”
),
“SALESAMOUNT”, \’SALES\'[SALESAMOUNT]
),
[ISGRANDTOTALROWTOTAL], 0,
\’DATE\'[MONTH NUMBER], 1,
\’DATE\'[MONTH NAME], 1
)
ORDER BY
[ISGRANDTOTALROWTOTAL] DESC,
\’DATE\'[MONTH NUMBER],
\’DATE\'[MONTH NAME]
尽管报告仅显示月份名称，但DAX查询同时包含月份名称和月份编号。实际上，结果已按月号正确排序。
现在，考虑连接到相同数据模型的Excel中的等效数据透视表，例如使用powerbi.com上的“在Excel中分析”功能。
MDX生成的查询如下：
SELECT
NON EMPTY
HIERARCHIZE(
{ DRILLDOWNLEVEL(
{ [DATE].[MONTH NAME].[ALL] },
,,
INCLUDE_CALC_MEMBERS
) }
)
DIMENSION PROPERTIES
PARENT_UNIQUE_NAME,
HIERARCHY_UNIQUE_NAME
ON COLUMNS
FROM [MODEL]
MDX查询仅包含月份名称。不必指定月份号，因为MDX保证结果中的月份名称属性已经按月份号排序了。
DAX查询中按列排序的副作用
两列的存在也会对过滤器上下文产生副作用。例如，参考以下方法：
FILTERMONTHNAME :=
ISFILTERED ( \’DATE\'[MONTH NAME] )
FILTERMONTHNUMBER :=
ISFILTERED ( \’DATE\'[MONTH NUMBER] )
WRONG % MONTHS :=
DIVIDE (
[SALESAMOUNT],
CALCULATE (
[SALESAMOUNT],
ALL ( \’DATE\'[MONTH NAME] )
)
)
通过在Power BI和Excel中比较相似报告的结果，可以发现一些差异。
错误月份百分比度量在Power BI（左侧）中不起作用，因为它始终返回100％，而在Excel（右侧）中则正确。
此外，ISFILTERED函数为两个客户端中的FilterMonthNumber列提供不同的结果：当选择月份名称（左侧）时，Power BI始终返回TRUE，而Excel在类似的报告中返回FALSE。原因是DAX在查询中同时包含两列–与MDX生成的过滤器上下文相比，这将生成不同的过滤器上下文来评估度量。
最后，当报表使用不带“月份名称”的“月份编号”列时，“月份名称”上的“按列排序”的存在不会引起任何副作用。以下Power BI报告显示，当在矩阵的行中使用月份号时，只有FilterMonthNumber度量返回TRUE，而不会影响FilterMonthName度量。
使用“按列排序”的度量的黄金法则
为了使使用“按列排序”设置操作列的措施具有可预测的作用，每次删除或操作过滤器时，最好始终包括“按列排序”设置涉及的两个列。
因此，如果要在单个列上编写过滤器测试，如：
ISFILTERED ( \’DATE\'[MONTH] )
ISFILTERED ( \’DATE\'[MONTH NUMBER] )
最好编写一个包含两列的单个表达式，例如：
ISFILTERED ( \’DATE\'[MONTH] ) || ISFILTERED ( \’DATE\'[MONTH NUMBER] )
如果要在单列上除去过滤器：
ALL ( \’DATE\'[MONTH] )
ALL ( \’DATE\'[MONTH NUMBER] )
最好编写一个包含两列的过滤器：
ALL ( \’DATE\'[MONTH], \’DATE\'[MONTH NUMBER] )
根据这些最佳实践进行了重写，以前的百分比度量如下所示：
CORRECT % MONTHS :=
DIVIDE (
[SALESAMOUNT],
CALCULATE (
[SALESAMOUNT],
ALL ( \’DATE\'[MONTH NAME], \’DATE\'[MONTH NUMBER] )
)
)
当在矩阵行中使用“月份名称”时，使用最后一项度量的Power BI报告按月显示正确的百分比。
结论
当DAX表达式从具有“按列排序”设置处于活动状态的列中删除过滤器时，最佳做法是始终将两个列都包括在ALL函数中（可参考ALL的功能）。为了测试是否对列进行了过滤，最佳做法是在“ 或”条件下包括两个ISFILTERED函数，测试“按列排序”设置中涉及的两个列。
获取案例文件
如果您喜欢该文章，并想要获取该文章源文件的朋友们，留言获取。
往期推荐
● 新DAX功能：CONVERT and REMOVEFILTERS
●【2019】10月份（广州站）Power BI 线下活动报名
技术交流
1.Power BI免费下载：http://www.yeacer.com/
Microsoft Power BI Desktop中文最新版：下载地址
2.欢迎加入的Power BI技术群，目前正在学习阶段，有兴趣的朋友可以一起学习讨论。
Power Data技术交流群：702966126 (验证注明：博客园Power BI)
更多精彩内容请关注微信公众号：悦策PowerBI
如果您觉得阅读本文对您有帮助，请点一下“推荐”按钮，您的“推荐”将是我最大的写作动力！欢迎各位转载，作者博客:https://www.cnblogs.com/yeacer/