新浪博客

使用GroupByColumns优化SWITCH对切片器的选择

2023-05-12 16:33阅读：

http://blog.sina.cn/dpool/blog/u/5967513441

本文翻译自Marco Russo & Alberto Ferrari的文章—《Optimizing SWITCH on slicer selection with Group By Columns》来源：SQLBI 本文介绍了如何使用Group By Columns属性，通过使用SWITCH函数中的相同列来存储切片器的选择，以优化查询性能。
几个月前，我们写了一篇关于DAX和Power BI中SWITCH优化的文章。我们解释说，当被分析的列在查询中被静态筛选时，引擎会产生SWITCH的最佳执行计划。翻译成Power BI工件，这意味着当你有一个切片器来选择参数时，SWITCH函数应该准确地使用切片器中显示的列，而不是用排序顺序的基础列（或同一表中的任何其他列）。
例如，考虑以下报告，其中切片器显示了一个度量列表，该列表控制在选定指数度量的矩阵中显示什么。


使用GroupByColumns优化SWITCH对切片器的选择



选定的指数度量是这样定义的：

销售表中的度量值
Selected Index =
SWITCH (
SELECTEDVALUE ( Options[Index] ),
1, [Sales Amount],
2, [Margin],
3, [Total Cost]
)

表Options有两列：Name列在切片器中显示，Index列用于对切片器中的名称进行排序，并在SWITCH中作为选定指数度量所使用的业务逻辑。


使用GroupByColumns优化SWITCH对切片器的选择



请记住，我们是用一个简单的例子来说明通过切片器在度量中注入参数的更普遍的情况：这个例子的具体使用将由Power BI中的字段参数来提供，但在这里我们需要关注SWITCH的性能差异，当它依赖于用户在报表上的选择时。
在切片器中，由于[Index]的顺序决定，Sales Amount在Margin之前；否则，切片器将按字母顺序显示[Name]的值。当你保存文件时，Power BI将选择存储为在[Name]上选择的 'Sales Amount'，忽略[Index]值。Power BI在查询中生成的筛选器如下：

TREATAS( { 'Sales Amount' }, 'Options'[Name] )

然而，你可能更喜欢使用不同于切片器中显示的列来编写业务逻辑。例如，你可能想写一个度量，即使你翻译或重命名[Name]中的描述，它也能工作。出于这个原因，选定索引度量通过使用[Index]来读取选择，[Name]列是用于排序的隐藏列。
正如在理解SWITCH的优化中所描述的那样，这种方法并不能提供最佳的性能，因为SWITCH是应用在一个取决于切片器中使用的列上。只有当该列与查询中筛选的列相同时，才能获得最佳查询方案。如果你发现这个性能问题击中了你的模型，而且你处于开发的高级阶段，改变现有的代码可能会很困难。然而，我们可以通过使用Group By Columns来改变Power BI的行为。

我们创建了一个名为 'Options Group By '的选项表的副本，以保持一个单一的样本文件。
使用GroupByColumns优化SWITCH对切片器的选择
在Options Group By表中，我们将Name的Group By Columns属性设置为按Index分组。
使用GroupByColumns优化SWITCH对切片器的选择

因为这个变化，Power BI不再使用它的值来存储'Options Group By'[Name]的选择：Power BI检索相应的'Options Group By'[Index]值，将其作为切片器的选择值存储，并在筛选上下文中用相应的选择筛选'Options Group By'[Index]。换句话说，Power BI显示的是Name，但它实际上存储和筛选了Index!

选定指数优化度量几乎与选定指数相同；唯一的区别是它对Options Group而不是Options起作用：

销售表中的度量值
Selected Index Optimized =
SWITCH (
SELECTEDVALUE ( 'Options Group By'[Index] ),
1, [Sales Amount],
2, [Margin],
3, [Total Cost]
)

Power BI在DAX查询中应用的筛选器使用'Options Group By'[Index]列，所以查询得到了优化：

TREATAS({1},'Options Group By'[Index])

我们在此不重复介绍SWITCH是如何优化查询计划的：本文的目的只是为了补充其他注意事项，这些注意事项对于优化仅由Power BI消耗的现有模型是有用的，因为MDX查询不支持Group By Columns属性，也不能用于Excel报表。

结论

Group By Columns属性可以改变用于存储切片器选择的列，这样显示的值可以在模型中被重新命名或翻译，而不会丢失应用于现有报表的选择。筛选器也直接应用于Group By Columns中使用的列，所以SWITCH函数即使读取了用户不可见的列的选择，也会得到一个优化的查询计划，只要它是Group By Columns中使用的列。这种技术较大的局限性是，该列不能在Excel报表中使用，因为Excel不会像Power BI为报表生成的DAX查询那样自动在MDX查询中添加底层列。

我的更多文章

下载客户端阅读体验更佳

APP专享