sql ssas mdx 导出结果_DAX中按列排序的另一种结果-白红宇

sql ssas mdx 导出结果_DAX中按列排序的另一种结果

阅读量：1539 次

发布时间：2019-04-21

本文共 2802 字，大约阅读时间需要 9 分钟。

今天，小悦和大家分享一篇国外知名大咖的一篇文章：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中的等效数据透视表，例如使用http://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度量。