Power Pivot 关系模型 知识点滴

Power Pivot 关系模型 筛选器 + 计算器

/ 度量值(Measure) /

(1) 筛选器

报告级、页面级、视觉级筛选器

切片器、行、列、图例

识别"初始"筛选条件

(2) 筛选条件

CALCULATE函数中"增删改"产生新的筛选条件

(3) 计算器

按公式执行计算

/ 理解上下文(Context) /

筛选上下文(Filter context) 对"列"进行筛选 如 [咖啡种类] = "拿铁"

行上下文(Row context) 对"行"进行筛选

(1) "行上下文" 不会自动转换成 "筛选上下文", 如需转换"必须"使用 CALCULATE 函数

(2) 度量值自带 CALCULATE 函数功能

/ CALCULATE 更改初始筛选条件 /

CALCULATE 创建 "New" filter context 并基于 "New" filter context 计算 Expression

CALCULATE ( Expression, Condition1, … ConditionN )

1. 复制 Original filter context -> "New" filter context

2. 计算 "New" filter context

   若 Condition 中 column 不在 Original filter context 则 追加 "New" filter context

若 Condition 中 column 存在 Original filter context 则 应用 "New" filter context 替换 "Original" filter context

3. 计算 Expression , 所有的 Condition 都被放在 Logic "AND" 来 创建 "New" filter context, 计算基于 "New" filter context

CALCULATE (

[销售量], -- 计算器, 执行计算, 用于对数据表中的数据列求和

'产品表'[咖啡种类] = "拿铁", -- 计算器漏斗

'产品表'[杯型] = "中" -- 计算器漏斗

)

CALCULATE (

[销售量], -- 计算器, 执行计算, 用于对数据表中的数据列求和

'产品表'[咖啡种类] in {"拿铁", "美式"} -- 计算器漏斗

)

CALCULATE (

[销售量], -- 计算器, 执行计算, 用于对数据表中的数据列求和

NOT '产品表'[咖啡种类] IN {"拿铁", "美式"} -- 计算器漏斗

)

工作过程

1. 识别初始筛选条件(初步筛选, 上下文) 即产品表中 [咖啡种类] = "卡布奇诺", [杯型] = "大"
2. 初始筛选的结果(#1) 落入"计算器漏斗" 进行二次筛选 并生成"新"筛选条件, 产品表中 [咖啡种类] = "拿铁", [杯型] = "中"
3. 透视表中每个单元格在"新"筛选条件下执行 [销售量] = sum('销售数据表'[数量]) 计算，即使"总计" 单元格也按照此逻辑

/ ALL 删除初始筛选条件(扩大筛选范围) /

[7 All销售量] = CALCULATE([1销售量], ALL('销售数据表')) -- 引用表, 清除引用表所有筛选条件

[7 All销售量] = CALCULATE([1销售量], ALL('产品表[咖啡种类]')) -- 引用列,

ALL函数清除的筛选列和初始筛选条件中的筛选列完全一致(同一张表的同一列); 引用列须来自同一张表

ALL 衍生函数:

[7 Allexcept] = CALCULATE([1销售量], ALLEXCEPT('产品表', '产品表'[杯型])) -- 除'产品表'[杯型]筛选条件"保留"外, 删除其他筛选条件

[7 Allexcept] = CALCULATE([1销售量], ALLSELECTED('产品表')) -- 直观合计, 只影响"合计"

/ FILTER 筛选器迭代函数(Iterator) 创建"行"上下文(Row Context) /

FILTER('表', 筛选条件)

FILTER('表', '表'[列]=公式) '表'[列] Filter 创建的行上下文 公式 初始筛选上下文

FILTER('表', '表'[列]=[度量值]) '表'[列] Filter 创建的行上下文 [度量值] Filter生成的行上下文转换为筛选上下文

10 Filter销售量 = CALCULATE([1 销售量], FILTER('门店信息表', [1 销售量] >= 200))

(1) 识别"初始"筛选条件 拿铁、2016Q2

(2) FILTER 逐行扫面'门店信息表' row(1)=北京市

(3) 根据数据模型关联关系, 计算 [1 销售量] 北京市销售量

(4) 若满足FILTER筛选条件则保留当前行, 否则删除

(5) 扫面下一行并循环执行(2)-(4)生成虚拟表

(6) 以(5)中生成的虚拟表为新筛选条件计算[1 销售量]