6.4 PowerBI系列之DAX函数专题 -调节器allselected和keepfilters的区别

森森森林呀

已于 2023-04-17 21:59:29 修改

阅读量483

点赞数

文章标签： powerbi

于 2023-04-13 19:38:49 首次发布

本文链接：https://blog.csdn.net/qq_33842426/article/details/130042151

版权

在Dax中，行，列，单元格，交互动作，以及模型关系都是函数操作的对象，理解这一点有助于代码的正确书写。

allselected

该函数会返回不包括当前视觉对象的行列筛选，但是会包括外部筛选的上下文。用于求个体占特定总体的比例时常常会用到。
Removes context filters from columns and rows in the current query, while retaining all other context filters or explicit filters.

The ALLSELECTED function gets the context that represents all rows and columns in the query, while keeping explicit filters and contexts other than row and column filters. This function can be used to obtain visual totals in queries.

ALLSELECTED([<tableName> | <columnName>[, <columnName>[, <columnName>[,…]]]] )

在这里插入图片描述
示例

define  
measure 'Reseller Sales'[Reseller Sales Amount]=sum('Reseller Sales'[Sales Amount])  
measure 'Reseller Sales'[Reseller Grand Total]=calculate(sum('Reseller Sales'[Sales Amount]), ALL('Reseller Sales'))  
measure 'Reseller Sales'[Reseller Visual Total]=calculate(sum('Reseller Sales'[Sales Amount]), ALLSELECTED())  
measure 'Reseller Sales'[Reseller Visual Total for All of Calendar Year]=calculate(sum('Reseller Sales'[Sales Amount]), ALLSELECTED('Date'[Calendar Year]))  
measure 'Reseller Sales'[Reseller Visual Total for All of Product Category Name]=calculate(sum('Reseller Sales'[Sales Amount]), ALLSELECTED('Product Category'[Product Category Name]))  
evaluate  
CalculateTable(  
    //CT table expression  
    summarize(
//summarize table expression  
crossjoin(distinct('Product Category'[Product Category Name]), distinct('Date'[Calendar Year]))  
//First Group by expression  
, 'Product Category'[Product Category Name]  
//Second Group by expression  
, 'Date'[Calendar Year]  
//Summary expressions  
, "Reseller Sales Amount", [Reseller Sales Amount]  
, "Reseller Grand Total", [Reseller Grand Total]  
, "Reseller Visual Total", [Reseller Visual Total]  
, "Reseller Visual Total for All of Calendar Year", [Reseller Visual Total for All of Calendar Year]  
, "Reseller Visual Total for All of Product Category Name", [Reseller Visual Total for All of Product Category Name]  
)  
//CT filters  
, 'Sales Territory'[Sales Territory Group]="Europe", 'Promotion'[Promotion Type]="Volume Discount"  
)  
order by [Product Category Name], [Calendar Year]

在这里插入图片描述

keepfilters

bookecase销售额 =calculate([销售额],'产品表'[商品次级类别]="bookcases") -- 在遇到商品刺激类别是，以calculate内部条件覆盖外部条件，因此只能得到bookcase的销售额

bookecase销售额_keepfilter = 
calculate(sum('订单表'[销售额]),
keepfilters('产品表'[商品次级类别]="bookcases")) // 保留外部上下文，和内部上下文联动
//calculate(sum('订单表'[销售额]),filter('产品表','产品表'[商品次级类别]="bookcases") //用filter函数也可以与外部上下文取交集
)

keepfilters与filters的区别：
keepfilters改变的是筛选器的作用方式
filter改变的是筛选的条件
在这里插入图片描述

kf_art&bookcases&chairs = calculate(sum('订单表'[销售额]),
											keepfilters('产品表'[商品次级类别] in('art','bookcases','chairs'))) --布尔型的内部筛选上下文和外部筛选上下文取交集，如果不要kf函数，那么商品小类字段只会使用内部筛选上下文
alls_art&bookcases&chairs = calculate(sum('订单表'[销售额]),
															filter(--"产品表",--filter参数直接传入产品表，可以结合外部上下文取交集
															allselected('产品表'[商品次级类别]),// 但如果用allselected函数，首先矩阵中只显示切片器中选择的商品小类，其次在切片器选择的商品小类和fillter参数中指定的商品小类之间去交集参与计算，但是对每一个显示出来的商品小类，都会被赋予最终交集小类的值
															'
															'产品表'[商品次级类别] in {"art","bookcases","chairs"})
												)

在这里插入图片描述