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函数,测试“按列排序”设置中涉及的两个列。

 

获取案例文件

如果您喜欢该文章,并想要获取该文章源文件的朋友们,留言获取。

 

 往期推荐

【2019】Power BI 9月产品功能更新视频

【2019】Power BI 9月产品功能更新文档

● 新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/ 
posted @ 2019-10-12 10:53  PowerBI365  阅读(1610)  评论(0编辑  收藏  举报