今天，小悦和大家分享一篇国外知名大咖的一篇文章：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函数，测试“按列排序”设置中涉及的两个列 。
?
获取案例文件
如果您喜欢该文章，并想要获取该文章源文件的朋友们，欢迎后台私信
活动报名