一、了解上下文
行上下文:行上下文指的是当前正在计算的那一行的数据。
筛选上下文:当前数据模型中的筛选状态,通常是由用户在数据透视表或其他可视化组件中所做的选择形成的。
二、DAX
(一)CALCULATE→ 筛选计算函数
顾名思义,既可以筛选,又可以计算。
【举例】假设你有一个销售数据表 Sales
,其中包括 Date
、Product
和 SalesAmount
字段。你可以创建一个计算度量值来表示总销售额:
1、疑惑问题的回答
- 与度量值的区别?
Total Sales = SUM(Sales[SalesAmount])
这个表达式本身并没有任何筛选逻辑。这意味着,如果你直接在报表中使用这个度量值而不附加任何额外的筛选条件,它将总是返回整个SalesAmount
列的总和,无论你在数据模型中的其他地方如何筛选数据。
- 不带筛选器
Total Sales by Filter = CALCULATE(SUM(Sales[SalesAmount])) ——不带筛选器
尽管这里没有明确给出筛选参数,但是CALCULATE函数会考虑来自报表或数据透视表的上下文筛选器。当这个度量值在一个数据透视表或可视化中使用时,它会根据用户在该报表上的选择(例如通过切片器、行或列标签等)来动态地改变其计算结果。
- 带筛选器:
Total Sales on Specific Date = CALCULATE(SUM(Sales[SalesAmount]), 'Sales'[Date] = "2024-09-10")
这个度量值总是计算2024年9月10日这一天的销售额,不管报表中的其他筛选条件是什么。即使用户在报表中选择了不同的日期或者其他过滤条件,这个度量值仍然只会返回2024年9月10日的销售额。
2、CALCULATE(【表达式】,【筛选器】)
(1)表达式可以是哪些?
- 聚合函数:如
SUM
、AVERAGE
、COUNT
、MIN
、MAX
等
Total Sales = CALCULATE(SUM(Sales[SalesAmount]))
- 度量值(Measures):已定义的度量值可以直接作为表达式。
- 复杂计算
Total Sales Weighted = CALCULATE(SUMX(Sales, Sales[Quantity] * Sales[Price]))
(2)筛选器可以是哪些?
- 单值筛选:如等于某个特定值。
Sales for Product A = CALCULATE(SUM(Sales[SalesAmount]), Sales[Product] = "Product A")
- 多值筛选:如包含多个值的列表。
Sales for Products A and B = CALCULATE(SUM(Sales[SalesAmount]), Sales[Product] IN {"Product A", "Product B"})
- 表达式:如使用
FILTER
或RELATEDTABLE
等函数来定义筛选条件。
Sales in Current Year = CALCULATE(SUM(Sales[SalesAmount]), FILTER(Sales, Sales[Year] = YEAR(TODAY())))
- 日期和时间智能筛选:如使用
DATESBETWEEN
、DATEADD
等函数来定义日期范围。
Sales in Last Quarter = CALCULATE(SUM(Sales[SalesAmount]), DATESBETWEEN(Sales[Date], TODAY() - QUARTER(YEAR(TODAY())), TODAY()))
- ALL函数:筛选器调节——移除指定列的筛选器,返回一个未受这些列筛选的表。
如果用户在数据透视表中选择了一个特定的颜色(例如红色),那么正常情况下 SUM('Sales'[SalesAmount]) 将只计算红色商品的销售额。但是,使用 ALL('Sales'[Color]) 后,CALCULATE 函数会忽略颜色的筛选条件,计算所有颜色的总销售额。
Total Sales All Dates = CALCULATE(SUM('Sales'[SalesAmount]), ALL('Sales'[Date]))
如果你想同时忽略颜色和地区的筛选条件,可以这样写:
Total Sales All Colors and Regions = CALCULATE(SUM('Sales'[SalesAmount]), ALL('Sales'[Color]), ALL('Sales'[Region]))
3.CALCULATETABLE(Expression, [Filter1], [Filter2], …)
- 与
CALCULATE
类似,但返回一个表而不是一个标量值。 - 例如,
CALCULATETABLE(SUMX(Sales, Sales[Amount]), Sales[Date] = "2024-09-10")
(二)FILTER
(【表】,【筛选器】)
计算某个特定产品(如Product A)的销售额:
Total Sales for Product A = CALCULATE( SUM(Sales[SalesAmount]), FILTER(Sales, Sales[Product] = "Product A") )
FILTER(Sales, Sales[Product] = "Product A")创建了一个新的表,该表仅包含产品为"Product A"的行。然后,CALCULATE函数使用这个筛选后的表来计算SalesAmount的总和。
【补充 FILTER
和CALCULATE筛选的区别】
CALCULATE
函数筛选条件简单(如基于列等于某个值或属于某个集合)。
Total Sales on Sep 10 = CALCULATE( SUM(Sales[SalesAmount]), 'Sales'[Date] = "2024-09-10" )
- FILTER函数筛选条件复杂,(如多个条件的组合、逻辑运算符的使用等)。
- FILTER函数返回一个经过筛选的新表,然后CALCULATE使用这个新表来执行计算。
Total Sales for Product A in Sep 1 to Sep 10 = CALCULATE( SUM(Sales[SalesAmount]), FILTER( Sales, Sales[Date] >= "2024-09-01" && Sales[Date] <= "2024-09-10" && Sales[Product] = "Product A" ) )
(三)关系函数
【补充】
1.多端找一端【RELATED
】
RELATED
从一个表获取与当前上下文相关的数据行,并从中提取某一列的值- LOOKUPVALUE(Table, Lookup_Column, Lookup_Value, Return_Column_Name)
Table:你要从中查找数据的表。
Lookup_Column:用于查找的列名。
Lookup_Value:要在Lookup_Column中查找的值。
Return_Column_Name:返回该列中的值。
查找特定值,并返回与之对应的另一个列的值。
【举例】
LOOKUPVALUE( Prices, Prices[ProductID],Sales[ProductID], Prices[LatestPrice])
在Prices表中查找与当前Sales行中的ProductID匹配的行,并返回该行的LatestPrice列的值。
2.一端找多端【RELATEDTABLE】
RELATEDTABLE获取与当前行相关的整个表的所有行。