文章目录

一、聚合函数

1.1 SUMX

参考《SUM vs SUMX》

1.1.1 简介

SUM是DAX中最常用的聚合函数，它只有一个参数：<column>，用于对数值列进行累加汇总，例如：

销售总额  = SUM( '订单表'[销售额] )

SUMX有两个参数，其语法为：

SUMX(<table>, <expression>)
  • <table>：将要进行计算的表，可以是FILTER表表达式计算得到的筛选表
  • <expression>：对表中每一行进行计算的表达式

  所以SUMX返回的就是：对表的每一行按表达式进行计算，再进行求和。SUM只是SUMX的一种简化形式，比如上面的公式可以写成：

销售总额 =SUMX( '订单表','订单表'[销售额] )

  由于SUM函数只有一个参数，决定了它只能处理一个表的一列的求和，而涉及到多列/多表的计算，或者对特定表达式的求和，它就不适用了。下面通过几个例子来看看仅用SUM不方便做到，而SUMX轻松实现的场景。

  1. 多列计算：在这个示例中，对于 Sales 表中的每一行，表达式 'Sales'[SalesAmount] * (1 + 'Sales'[TaxRate]) 计算了包含税的销售额，然后 SUMX 函数对这些值求和。

    TotalSalesWithTax =
SUMX(
    'Sales',
    'Sales'[SalesAmount] * (1 + 'Sales'[TaxRate])
)

  2. 多表计算
    当需要对相关表中的数据进行求和时，SUMX 函数也很有用。例如，订单表中只有销售数量，需要乘以在产品表中的单价才能获得销售额（产品表与订单表建立一对多的单向关系），这时就更适合用SUMX了，它的第二参数可以用表达式：

    销售额=SUMX('订单表', '订单表'[数量]*RELATED('产品表'[单价])

  3. 对筛选表进行求和
    如果只是求某一类数据的总和，可以利用FILTER先筛选订单表，作为SUMX的第一个参数，然后对筛选后的行求和。比如计算订单表中"耳机"产品的销售额，可以这样写：

    销售额 耳机 =
SUMX(
    FILTER('订单表','订单表'[产品名称]="耳机"),
    '订单表'[销售额]
)

  除了SUM有SUMX，对于其他几个常用的聚合函数，MIN\MAX\AVERAGE等，也有个对应的X函数，它们之前的区别都可参考本文。

1.1.2 累计聚合

本节要计算的是历史至今的累计，模拟数据如下：

在这里插入图片描述
为了能一次计算出各种粒度的历史至今，可以先构造一个日期表并建立关系，历史至今累计收入为：

历史至今 累计收入 = 
CALCULATE(
    SUM('订单表'[销售额]),
    FILTER(
            ALL('日期表'),
            '日期表'[日期]<=MAX('日期表'[日期])))

但是此方式会把日期表中所有的日期都计算一遍，其实大于最后一个订单日期的无须计算。可以先找出最后一个订单日期，然后在历史累计中增加判断条件：

历史至今 累计收入 优化 = 
IF(
    MIN('日期表'[日期])<=[订单最大日期],
    CALCULATE(
        SUM('订单表'[销售额]),
        FILTER(
             ALL('日期表'),
             '日期表'[日期]<=MAX('日期表'[日期]))))

在这里插入图片描述

1.1.3 滚动聚合

  滚动聚合，比如常用的滚动年度总计（MAT,Moving Annual Total），是求向前滚动12月的合计数，经常用于财务上的滚动预算，其计算方式为：

MAT  =
IF(MIN('日期表'[日期])<=[订单最大日期],
    CALCULATE(
        SUM('订单表'[销售额]),
        DATESINPERIOD(
            '日期表'[日期],
            LASTDATE('日期表'[日期]),-1,YEAR)
       )
)

  这里的关键是时间智能函数DATESINPERIOD，利用它返回向前滚动一整年的日期。数据从2018年1月开始，所以2018年12月之前的滚动求和都是不满12个月。如果要求不满12月，就不要显示数据，可以改写为：

MAT 优化 =
IF(MIN('日期表'[日期])<=[订单最大日期],
    CALCULATE(
        IF(
            COUNTROWS(
                FILTER(VALUES('日期表'[月份]),[收入]>0)
            )=12,
           [收入]),
        DATESINPERIOD(
            '日期表'[日期],
            LASTDATE('日期表'[日期]),-1,YEAR)
    )
)

在这里插入图片描述

1.2 AVERAGEX（待补）

参考《一文掌握Power BI中平均值的计算》

1.3 DISTINCTCOUNT

  COUNTROWS函数可用于统计表中的行数，比如订单表行数 = COUNTROWS('订单表')，而DISTINCTCOUNT 用于统计列中的唯一值数量（去重计数）。如果需要统计订单表中不重复的客户数量，可以使用以下公式：

客户数量 = DISTINCTCOUNT('订单表'[客户姓名])

这等价于：

// 先利用DISTINCT函数返回客户姓名的不重复列表，然后利用COUNTROWS计算这个表的行数
客户数量 = COUNTROWS(DISTINCT('订单表'[客户姓名]))

1.4 用户定义的聚合（待补）

参考《用户定义的聚合》、kimi

二、统计函数

2.1 RANKX

RANKX函数用于对整个表或表的子集进行排名，其语法为：

RANKX(<table>, <expression>[, <value>[, <order>[, <ties>]]])
  • <table>：要进行排名的表。
  • <expression>：用于计算排名的表达式，通常是表中的某一列。
  • <value>（可选）：用于比较的值。如果不指定，则默认为当前行的值。
  • <order>（可选）：指定排序顺序，DESC 表示升序（默认），ASC 表示降序。
  • <ties>（可选）：指定如何处理平级的情况，SKIP 表示跳过平级（非连续排名，默认），DENSE 表示密集排名（连续排名）。
2.1.1 绝对排名与相对排名

以销售明细表的产品名称为行标签，则：

[销售总额] =SUM('销售明细'[销售额])

总体绝对排名 = RANKX(ALL('销售明细'[产品名称]),[销售总额])

按照所选的产品范围来排序，就是相对排名，可以结合ALLSELECT函数来实现：

总体相对排名 = RANKX(ALLSELECTED('销售明细'[产品名称]),[销售总额])

在这里插入图片描述

  如果写作RANKX(ALLSELECTED('销售明细'),[销售总额])，则所有排名都是1，因为清除所有筛选器后，度量值销售总额= sum('销售明细'[销售额])的计算结果都是一样的。

2.1.2 类别内部排名

在行标签中加入门店城市字段，报表中加入门店城市切片器。则各个门店内部的产品排名为：

产品绝对排名（总体） = RANKX(ALL('销售明细'[产品名称],'销售明细'[门店城市]),[销售总额])
产品相对排名（总体） = RANKX(ALLSELECTED('销售明细'[产品名称],'销售明细'[门店城市]),[销售总额])
产品绝对排名（门店） = RANKX(ALL('销售明细'[产品名称]),[销售总额])
产品相对排名（门店） = RANKX(ALLSELECTED('销售明细'[产品名称]),[销售总额])

在这里插入图片描述

2.1.3 类别整体排名

如果想知道每个类别整体的销售情况排名，那么就需要计算出每个类别的整体销售额：

门店绝对排名 = 
RANKX(
    ALL('销售明细'[门店城市]),
    CALCULATE(
        [销售总额],
        ALLEXCEPT('销售明细','销售明细'[门店城市])
    )
)

门店相对排名 = 
RANKX(
    ALLSELECTED('销售明细'[门店城市]),
    CALCULATE(
        [销售总额],
        ALLEXCEPT('销售明细','销售明细'[门店城市])
    )
)

在这里插入图片描述

  • 对哪个维度进行排名，就构建一个对应的维度表，作为RANKX的第一个参数，动态就用ALLSELECTED；

  • 按什么排名，就写对应的表达式，作为RANKX的第二个参数。

2.1.4 计算层级排名

在2.4.3章节中，介绍过层级占比，这里的实现思路也是一样的：

层级排名（绝对） = SWITCH(
    TRUE(),
    ISINSCOPE('销售明细'[产品名称]),RANKX(ALL('销售明细'[产品名称]),[销售总额]),
    ISINSCOPE('销售明细'[门店城市]),RANKX(ALL('销售明细'[门店城市]),CALCULATE([销售总额],ALLEXCEPT('销售明细','销售明细'[门店城市]))),
    RANKX(ALL('销售明细'[产品名称]),[销售总额])											//条件都不满足时的默认值
)

层级排名（相对） = SWITCH(
    TRUE(),
    ISINSCOPE('销售明细'[产品名称]),RANKX(ALLSELECTED('销售明细'[产品名称]),[销售总额]),
    ISINSCOPE('销售明细'[门店城市]),RANKX(ALLSELECTED('销售明细'[门店城市]),CALCULATE([销售总额],ALLEXCEPT('销售明细','销售明细'[门店城市]))),
    RANKX(ALL('销售明细'[产品名称]),[销售总额])

在这里插入图片描述

产品绝对排名（总体）计算有误，暂时不知道原因。

2.1.4 动态展示前N名

首先写出总计排名的公式：

总体排名 = RANKX(ALL('销售明细'[产品名称]),[销售总额])

如果只想显示前5名，可以写作：

销售额 前5名= 
CALCULATE(
    [销售总额],
    FILTER(VALUES('销售明细'[产品名称]),[总体排名]<=5)
)

如果想利用参数动态显示前N名，先创建一个参数：
在这里插入图片描述

  此时在画布上会自动出现一个切片器（新建参数时，左下角有个默认选项：将切片器添加到此页）。重新写一个度量值，把N替换为刚建立的参数，就可以随心所欲的查看前N名了。

销售额 前N名 = 
CALCULATE(
    [销售总额],
    FILTER(
        VALUES('销售明细'[产品名称]),
        [总体排名]<=SELECTEDVALUE('参数'[参数])  //  SELECTEDVALUE('参数'[参数])表示切片器中被选中的参数值
    )
)

在这里插入图片描述

为了突出显示销冠产品是哪个，还可以使用卡片图来展示：

销量第一的产品 = 
CALCULATE(
    FIRSTNONBLANK('销售明细'[产品名称],1),
    FILTER('销售明细',[总体排名]=1)
)

在这里插入图片描述
所有的这些排名都是可以随其他维度的筛选动态变化的，比如不同时间段的排名情况：

在这里插入图片描述

2.1.5 使用关联表数据进行排名

假设我们有以下Product表和 InternetSales 表，现在需要在产品表中，根据其销售额进行排名。
在这里插入图片描述

  1. 新建列，获取每种产品的销售额：
TotalSales = SUMX(RELATEDTABLE(InternetSales), [SalesAmount])
  • RELATEDTABLE(InternetSales)：返回与当前产品相关的 InternetSales 表中的所有行。
  • SUMX(RELATEDTABLE(InternetSales), [SalesAmount])：对于 Products 表中的每一行，找到与该产品相关的 InternetSales 表中的所有行，然后对这些行的 SalesAmount 列求和。
  1. 在产品表中，根据产品的销售额进行排序：
Ranking = RANKX(ALL('Product'),[TotalSales])

你可以将其简写为一列：

Ranking = RANKX(ALL('Product'),SUMX(RELATEDTABLE(InternetSales), [SalesAmount]))

  这个公式的作用是，对 Products 表中的每一行，计算该产品在 InternetSales 表中的总销售额；然后根据这些总销售额在整个 Products 表中进行排名。最终结果为：
在这里插入图片描述

2.2 TOPN（待补）

参考《10个常用的业务度量值》

TOPN用于返回指定表的前 N 行，比如返回下表最新业务日期上一个交易日：

在这里插入图片描述

最新业务日期的上一个交易日 =
CALCULATE(
    MIN('订单表'[日期]),
    TOPN(2,ALL('订单表'),'订单表'[日期])
)

单笔最大订单对应的客户 =
CALCULATE(
    MAX('订单表'[客户]),
    TOPN(1,'订单表',[销售额合计]))

如果有多个客户，可以使用CONCATENATEX函数进行多值连接（见本文4.2）。

// 内层TOPN返回销售额最大的2笔订单，然后外层的TOPN从其中筛选出较小的那一行。
单笔第二大订单对应的客户 =
CALCULATE(
    MAX('订单表'[客户]),
    TOPN(
        1,
       TOPN(2,'订单表',[销售额合计]),
       [销售额合计],
       ASC
    )
)

2.3 PERCENTILE.INC与PERCENTILE.EXC（百分位计数）

  百分位是统计学中用于衡量一个数据点在一组数据中的相对位置的指标。例如，25% 分位表示数据中有 25% 的值低于这个数值。Power BI 提供了两个计算百分位的 DAX 函数，其语法是一样的：

// column 为数据列，k为百分位值
PERCENTILE.INC(<column>, <k>)			// 计算百分位时包括端点值
PERCENTILE.EXC(<column>, <k>)			// 计算百分位时排除端点值

以下面这个简单的数据为例，应用这两个函数，计算结果并不一样：

25%分位-INC = PERCENTILE.INC( '表'[数据], 0.25 )
25%分位-EXC = PERCENTILE.EXC( '表'[数据], 0.25 )

在这里插入图片描述

  • PERCENTILE.INC 的计算逻辑：
    1. 按升序排列数据。
    2. 计算位置：k×(N−1)+1=0.25×(10-1)+1=3.25，3.25介于第3和第4个位置之间，对应值为30和40
    3. 使用线性插值计算结果：30+0.25×(40−30)=32.5
  • PERCENTILE.EXC 的计算逻辑：
    1. 按升序排列数据。
    2. 计算位置：k×(N+1)=0.25×(10+1)=2.75，2.75介于第2和第3个位置之间，对应值为20和30
    3. 使用线性插值计算结果，20+0.0.75×(30−20)=27.5

  k值越远离0.5，二者的计算差异越大，越靠近0.5，差异越小，当k值等于0.5时，二者的结果是一致的，都返回的是中位数。对于PERCENTILE.INC，它的k值可以从0到1的任意一个值（包括0和1）；而PERCENTILE.EXC的k值只能在1/(N+1)与N/(N+1)之间，超出这个范围将报错（不包含端点值，N是数据点的数量）。一般来说，PERCENTILE.INC 更适合离散分布的有限数据集，更适用于分布较均匀或数据点较少的情况。而PERCENTILE.EXC 的算法更加适合更适合用于连续分布的样本数据，因其排除端点，能够避免极端值的影响。

三、表操作函数

3.1 ADDCOLUMNS

ADDCOLUMNS用于在现有的表格基础上添加新的计算列

ADDCOLUMNS(<table>, <name1>, <expression1>[, <name2>, <expression2>]…)
  • <table>：要操作的表
  • <name>, <expression>：新列的名称及新列的生成表达式。
3.1.1 添加多列

以下代码使用ADDCOLUMNS直接制作了一个日期表：

日期表 =ADDCOLUMNS (
CALENDAR (DATE(2017,1,1), DATE(2018,12,31)),
	"年度", YEAR ( [Date] ),
	"季度", "Q" & FORMAT ( [Date], "Q" ),
	"月份", FORMAT ( [Date], "MM" ),
	"日",FORMAT ( [Date], "DD" ),
	"年度季度", FORMAT ( [Date], "YYYY" ) & "Q" & FORMAT ( [Date], "Q" ),
	"年度月份", FORMAT ( [Date], "YYYY/MM" ),
	"星期几", WEEKDAY ( [Date],2 )
)

在这里插入图片描述

3.1.2 结合CALCULATE 实现复杂计算

ADDCOLUMNS 函数中的表达式可以包含 CALCULATE 函数，从而在添加新列时应用更复杂的计算逻辑。例如，你可以添加一个列来计算每个产品的利润：

产品利润 =
ADDCOLUMNS(
    Sales,
    "Profit", CALCULATE(SUM(Sales[Amount]) - SUM(Sales[Cost]), Sales[Product] = EARLIER(Sales[Product]))
)

在这个例子中，Profit 列的值是通过计算每个产品的销售额减去成本得到的。

  • 性能考虑: 在使用 ADDCOLUMNS 时，要注意其对性能的影响，尤其是在处理大量数据时。复杂的计算表达式可能会导致查询变慢。
  • 上下文转换: ADDCOLUMNS 函数中的表达式在计算时会受到当前上下文的影响，因此需要正确理解行上下文和筛选上下文的转换。

3.2 SUMMARIZE

SUMMARIZE函数用于对数据进行分组和汇总，其语法为：

SUMMARIZE(
    <Table>,          // 要进行汇总的表
    <GroupBy_Expression1>, ...,  // 分组表达式，可以有多个
    <Name1>, <Expression1>, ...  // 定义新列的名称和表达式，可以有多个
    [, <Filter_Expression>]       // 可选的过滤表达式
)

  • 提取多列的有效组合时，可以使用SUMMARIZE

  • 返回汇总表时，推荐使用SUMMARIZECOLUMNS

3.2.1 提取维度表

  当只使用前两个参数（表、表中的单个列）的时候，会返回该列的非重复值列表，功能与VALUES相似。如果是多个列，会返回这些列的有效组合（不存在的字段组合不会出现）。比如查看销售明细表中，各个产品哪些天有在销售。

  1. 截取部分销售数据
销售子表 = FILTER('销售明细','销售明细'[门店城市]="广州市"&&'销售明细'[订单日期]>DATE(2016, 11, 24)&&'销售明细'[订单日期]<DATE(2016, 12, 6))

在这里插入图片描述

  1. 查看产品销售情况：
销售时间表 = SUMMARIZE('销售子表','销售子表'[产品名称],'日期表'[年度月份])

在这里插入图片描述

3.2.2 返回汇总表（不推荐）

如果除了表和表的列名，还添加了新列名及其计算表达式，比如计算总和，就可以生成汇总表：

销售汇总表 = SUMMARIZE('销售子表','销售子表'[产品名称],'日期表'[年度月份],"销售额合计",SUM('销售子表'[销售额]))

在这里插入图片描述
在上面的表达式中的分组列外面套一层ROLLUP，可以生成合计行。

销售汇总表 =
SUMMARIZE (
    '销售子表',
    ROLLUP ( '日期表'[年度月份], '销售子表'[产品名称] ),
    "销售额合计", SUM ( '销售子表'[销售额] )
)

在这里插入图片描述
在上面表达式ROLLUP里面再套一层ROLLUPGROUP，就会发现分组的小计不见了，只返回了总计。

销售汇总表 =
SUMMARIZE (
    '销售子表',
    ROLLUP ( ROLLUPGROUP ( '日期表'[年度月份], '销售子表'[产品名称] ) ),
    "销售额合计", SUM ( '销售子表'[销售额] )
)

在这里插入图片描述

3.2.3 ADDCOLUMNS结合SUMMARIZE

  使用SUMMARIZE函数来增加扩展列的计算非常复杂，新增列的计算同时处于行上下文（row context）和过滤上下文（filter context）中，以至于结果可能出乎意料。比如，第一次测试时OK，但是部署到生产环境中就会出错，且难以调试。所以，出于性能和内部兼容性等方面的原因，并不建议使用它来进行汇总，可以使用ADDCOLUMNS和SUMMARIZE组合来代替。

  你可以先使用 SUMMARIZE 对销售数据按产品进行分组汇总，然后使用 ADDCOLUMNS 添加一个计算列来显示每个产品的销售额，结果是一样的。

销售汇总表 =
ADDCOLUMNS(
    SUMMARIZE(
        '销售子表','销售子表'[产品名称],'日期表'[年度月份]),
    "销售额合计",
    CALCULATE(SUM('销售子表'[销售额]))
)
  • SUMMARIZE 函数：将 销售子表 按照 产品名称 和 年度月份 进行分组。
  • ADDCOLUMNS(..., "销售额合计", CALCULATE(...))：在 SUMMARIZE 函数生成的汇总表基础上添加一个名为 销售额合计 的新列
  • CALCULATE(SUM('销售子表'[销售额]))：CALCULATE 函数用于在指定的上下文中计算表达式，这里它确保了计算是在 SUMMARIZE 函数定义的分组上下文中进行的。
3.2.4 使用SUMMARIZECOLUMNS进行汇总（推荐）

SUMMARIZECOLUMNS也是用于对数据进行分组和汇总。它允许你基于一个或多个列创建汇总表，并对这些组进行计算，其语法为：

SUMMARIZECOLUMNS(
    <columns>[,...],                // 要分组的列，必须是表中的列名
    [<filter1>], [<filter2>], ...,  // 筛选条件，可选，用于限制汇总的数据范围
    [<name1>, <expression1>], ...   // 汇总列，可选，用于在汇总表中添加新的计算列（列名+计算表达式）
)

SUMMARIZECOLUMNS第一个参数不再需要表，直接就是分组列，写起来更简洁，且性能优于ADDCOLUMNS和SUMMARIZE组合，也远优于SUMMARIZE，比如只汇总零售价大于2000的记录

销售汇总表 = SUMMARIZECOLUMNS(
    '销售子表'[产品名称],'日期表'[年度月份],
    FILTER('销售子表','销售子表'[零售价]>2000),
    "销售额合计",
    CALCULATE(SUM('销售子表'[销售额]))
)

在这里插入图片描述

3.2.5 示例：统计客户表

在这里插入图片描述

  对于上表，仅购买过一个产品类目的客户有哪些？购买过多个产品类目的客户有哪些？分别购买了哪些类目？这个问题的关键是先统计出每个客户购买了几个类目（使用SUMMARIZE进行统计），然后使用FILTER返回返回购买了一个和多个类目的客户列表就行了。

在这里插入图片描述
对于购买了多个类目的客户，还要列出来购买了哪些类目，可以在上面这个代码的基础上优化一下：
在这里插入图片描述

  但是通过这种方式返回的客户列表是固定的，因为它的上下文已经被固化了如果想要动态的实现需求，比如按照日期切片器，计算不同时间段的客户列表，只需要建两个简单度量值：

类目数量 = DISTINCTCOUNT('订单表'[产品类目])

类目列表 =
CONCATENATEX(
SUMMARIZE('订单表','客户表'[客户],'订单表'[产品类目]),
'订单表'[产品类目],"、")

把这两个度量值放入到矩阵中，客户名称作为行标题，就可以实现动态筛选：

在这里插入图片描述

3.3 VALUES 与 DISTINCT

  VALUES 与 DISTINCT语法相同，参数可以是表或者表中的列，主要功能都是用于提取唯一值，但在处理数据模型关系和上下文时存在区别。

函数 返回结果 说明
VALUES(Column) 返回列的唯一值列表，考虑筛选器和空白行（如果存在） 如果某列包含关联表中不存在的值
计算引擎会添加一个空白项来表示这种不匹配
VALUES(TableName) 返回表的所有行，再加一个空白行（如果存在不匹配情况） 仅接受表引用
DISTINCT(Column) 返回唯一值列表，考虑筛选器但不考虑空白行 纯粹基于列本身的去重，不检查关联表的有效性。
DISTINCT(Table) 返回表的唯一行，不考虑空白行 接受任何有效的表表达式。

  VALUES会检测关系断裂导致的空白，适合需要完整性检查的场景，如计算存在缺失关联的维度。DISTINCT忽略关系断裂，适用于简单的去重计数。两者返回的唯一值均受筛选器的影响，若要清除这种影响，可使用ALL 函数。

3.4 表连接函数

函数 定义 性能影响 适用场景
NATURALINNERJOIN 基于表之间的公共列，返回两个表的匹配行 较快，基于已有的关系进行连接 用于需要基于表之间关系合并数据的场景。
NATURALLEFTOUTERJOIN 基于表之间的公共列
返回左表的所有行和右表的匹配行		 较快，基于已有的关系进行连接 使用右表数据匹配左表
INTERSECT 不依赖表之间的关系
直接比较两个表的行（下同），返回交集		 较快，只需要找到共同的行。 用于找出两个表的共同部分。
UNION 返回两个表的并集 较慢，需要去重。 用于合并两个表的数据。
EXCEPT 返回第一个表中不在第二个表中的行。 较快 用于找出第一个表中不在第二个表中的行。
CROSSJOIN 返回两个表的笛卡尔乘积（所有可能的行组合） 较慢 通常用于需要全面组合分析的场景。
3.4.1 求两个表的交集、并集、差集、对称差集
  • UNION(<table_expression1>, <table_expression2> [,<table_expression>]…)：简单合并表，可以同时合并多个。table_expression表示可以是任何返回表的表达式。
  • DISTINCT(<table>)：保留表中不重复的行
  • INTERSECT(<table_expression1>, <table_expression2>)：返回两个表的交集
  • EXCEPT(<table_expression1>, <table_expression2>)：返回出现在第一个表但未出现在第二个表的数据，需要剔除数据时十分有用。

模拟一个简单的订单表如下：

在这里插入图片描述
使用以下代码构建两个子表，分别表示购买了产品A、产品B的客户列表：

A产品客户表 = CALCULATETABLE(
    VALUES('产品订单表'[客户]),
    FILTER('产品订单表','产品订单表'[产品名称]="A"))   // 结果为 "甲乙丙丁"

B产品客户表 = CALCULATETABLE(
    VALUES('产品订单表'[客户]),
    FILTER('产品订单表','产品订单表'[产品名称]="B"))  // 结果为 "乙丁戊己"
  1. 购买A或购买B的客户：其实就是求并集。利用UNION函数把A客户表和B客户表简单合并，然后使用DISTINCT函数去重，就得到了购买A或者B的客户列表。
A并B客户表 = DISTINCT(UNION('A产品客户表','B产品客户表'))  // 结果为 "甲乙丙丁戊己"
  1. 购买A且购买B的客户：求二者交集，结果为"乙丁"。
A交B客户表 = DISTINCT(INTERSECT(customerA,customerB))     // 结果为"乙丁"

如果你使用了DISTINCT(NATURALINNERJOIN(customerA,customerB)) ，会报错：
在这里插入图片描述

  1. 购买A但未购买B的客户：也就是求A差B。这里要使用EXCEPT函数，用于返回出现在第一个表但未出现在第二个表的数据，需要剔除数据时十分有用。
A差B客户表 = EXCEPT('A产品客户表','B产品客户表')  		//结果为 "甲丙"
  1. 仅购买A或B其中一种的客户：将A并B - A交B就行。
对称差集客户表 = EXCEPT('A并B客户表','A交B客户表')   			// 结果为 "甲丙戊己"

在这里插入图片描述

又比如，计算不同期间的共同客户，也可以用INTERSECT：
在这里插入图片描述

客户数量 = COUNTROWS(VALUES('订单表'[客户姓名]))
客户数量 PY = CALCULATE( [客户数量] , SAMEPERIODLASTYEAR('日期表'[日期])   // 去年同期客户数量

共同客户数量 =
	VAR customers=VALUES('订单表'[客户姓名])
	VAR customers_py=CALCULATETABLE(VALUES('订单表'[客户姓名]),SAMEPERIODLASTYEAR('日期表'[日期]))
	VAR customers_same=INTERSECT(customers,customers_py)
RETURN COUNTROWS(customers_same)

在这里插入图片描述

3.4.2 维度表筛选事实表

  在PowerBI中，可以按多个维度对数据进行筛选。还有一种常见方式，是使用维度表对事实表进行筛选。假设有一张模拟的订单表（事实表），还有一张条件表（维度表）：

在这里插入图片描述
  现在需要根据这个筛选表，来找出订单表中的记录，即找出1月2日产品A和B、以及1月3日产品B的销售记录。这个问题相当于求两个表的交集，可以使用NATURALINNERJOIN来实现。

在这里插入图片描述
  可以看到事实表和维度表由于没有建立关系，没有公共联接列，所以无法返回正确的结果。但是两个表之间只能有一个活动关系，无法同时进行两个维度的筛选。一种做法是通过两个维度表建立关系，然后再用NATURALINNERJOIN就可以正常提取出需要的数据了。

在这里插入图片描述

可以用SELECTCOLUMNS函数来选择结果表中非重复的列。

在这里插入图片描述

3.5 TREATAS（多条件筛选）

  对于上一节的问题，还有一种方式是使用TREATAS函数。TREATAS 函数允许在两个没有直接关系的产品表之间创建一个虚拟的筛选关系（实体关系和虚拟关系），其语法为：

TREATAS(table_expression, <column>[, <column>[, <column>[,…]]]} )
  • table_expression：可以是任何返回表的表达式。如果表达式中返回的值在列中不存在，则忽略该值。
  • column：必须是现有列，不能是表达式。指定的列数必须与表表达式中的列数匹配，并且顺序相同。
  • 如果涉及的表之间存在多个关系，请考虑改用 USERELATIONSHIP

以上一节的示例为例，订单表和条件表之间不需要建立关系，使用以下代码可以得到同样的结果：

在这里插入图片描述

  TREATAS可以理解为TREAT+AS：将xx视同为xx的意思。上图代码中，它将“筛选条件表”，视同为订单表中的订单日期列和产品名称列，用订单表中的两列来筛选订单表，当然是能返回正确的结果的。

  TREATAS可以在不破坏原有数据模型的前提下，根据需要，在公式内部灵活的构建虚拟关系，来进行筛选计算。但它的灵活性也是有代价的，在虚拟关系下运行的速度比实体关系下要慢很多，当数据量较大时会明显的感觉到，所以在数据模型中建立实体关系总是很必要的。

3.6 制作辅助表的几种方式

参考《Power BI 辅助表制作方式汇总》

四、关系函数

4.1 RELATED 与 RELATEDTABLE

参考《RELATED、LOOKUPVALUE报错？它才是最通用的函数》

4.1.1 一维关系匹配

  RELATED函数用于生成计算列，一般是把维度表的数据，匹配到事实表中，也就是沿着关系的多端找一端的值。以下面以这个简易的订单表和汇率表为例，需要在订单表中匹配到对应的汇率，可以在订单表中新建计算列（需要先建立一对多的关系）：

汇率 = RELATED('汇率表'[汇率])

在这里插入图片描述

在这里插入图片描述

  如果反过来，想把事实表的数据，匹配到维度表，即沿着关系的一端找多端的值，可以使用RELATEDTABLE函数。不过RELATEDTABLE返回的是一个表，不能直接用于创建计算列，需要把这个函数返回的表进行聚合。比如在汇率表中，使用新建列，计算每个币种的订单数量：

订单量 = COUNTROWS(RELATEDTABLE('订单表'))

  但是如果汇率表增加一个维度，不仅有币种，在不同的年月也是不同的，需要两个维度匹配才能得到汇率。如果还是用币种建立关系，只能是多对多关系，这种情况下，用RELATED函数是会报错：“列不存在，或与当前上下文中的可用表没有关系”。真实原因是：RELATED函数只能在一对多的关系中使用，如果不存在一对多的关系，将会报错。

在这里插入图片描述

并且对于新的汇率表，还需要按两个维度来匹配，RELATED函数无法做到，这种情况下可以用LOOKUPVALUE函数来实现。

4.1.2 使用LOOKUPVALUE进行两个维度的匹配

LOOKUPVALUE函数用法与VLOOKUP类似，其语法为：

LOOKUPVALUE (
    <result_columnName>,							// 需要返回的列（结果列）
    <search_columnName>,<search_value>				// 指定要查找的列和对应的值，存在多个匹配项时返回第一个
    [, <search2_columnName>, <search2_value>]…
    [, <alternateResult>]							// 未找到匹配项时的指定值，默认为BLANK
)

在订单表中新建列，输入：

汇率 =
LOOKUPVALUE(
    '汇率表'[汇率],				// 从汇率表中查找对应的汇率值
    '汇率表'[年月],[年月],
    '汇率表'[币种],[币种]
)

在这里插入图片描述

LOOKUPVALUE函数不依赖于关系，无论是什么类型的关系，甚至不建立关系，都不影响，并且它可以按多个维度匹配。

4.1.3 匹配值不唯一

如果每个月有多个汇率，即匹配的结果不唯一，更通用的做法是CALCULATE+FILTER组合，对于多值情况，返回其均值：

汇率 =
VAR currency_=[币种]
VAR yearmonth_=[年月]
RETURN
CALCULATE(
    AVERAGE('汇率表'[汇率]),
    FILTER(
        '汇率表',
        '汇率表'[币种]=currency_&&'汇率表'[年月]=yearmonth_
    )
)

在这里插入图片描述
CALCULATE+FILTER组合是更普适的做法，无论建立的是什么关系，无论多少个条件、无论查询结果是否唯一，都可以轻松处理。

4.1.4 使用度量值

  上面都是用的计算列的方式，其实最后这个需求更适合用度量值来实现，第一步是建立模型。对于上面的订单表和汇率表，有两个分析维度，就是年月和币种，先建两个维度表，然后分别与两个事实表相连。只需要用聚合函数写个简单的度量值：

汇率 度量值 = AVERAGE('汇率表'[汇率])

在这里插入图片描述

  • 一对多关系的单维度匹配，可以用RELATED函数创建计算列;
  • 任意关系、多个维度的匹配，查询结果唯一，可以用LOOKUPVALUE函数创建计算列；
  • 任意关系、任意多个维度的匹配，无论查询结果是否唯一，都可以使用CALCULATE+FILTER组合来实现；
  • 能用度量值尽量使用度量值，模型和度量值的配合通常可以更简单地实现同样的需求、以及更好的性能。

4.2 USERELATIONSHIP（临时激活非活动关系）

  在PowerBI中，两个表之间可以有多个关系，但只能有一个活动关系，其它都是非活动关系，非活动状态不会自动用于数据筛选和计算。在 Power BI Desktop 模型视图中，活动关系用实线表示；非活动关系用虚线表示。例如下图中，订单表中有订单日期以及发货日期，两者与日期表的日期列分建立了活动关系和非活动关系。

在这里插入图片描述

  默认情况下，无法按订单日期进行筛选。如果想这么做，一种方式是在模型视图中，将订单日期关系设为非活动，再手动设置发货日期为活动关系。另一种方式是使用USERELATIONSHIP函数，激活两列之间的非活动关系，其语法为：

USERELATIONSHIP(<columnName1>,<columnName2>)
  • 两列的顺序无所谓，不影响计算结果，但必须以及建立了非活动关系。
  • 激活非活动关系后，会自动断开原有的活动关系，但只在该表达式内部生效

我们可以测试一下：

收入 = SUM( '订单表'[销售额] )

收入 按发货日期 = 
CALCULATE(
    [收入],
    USERELATIONSHIP('日期表'[日期],'订单表'[发货日期]))

在这里插入图片描述

  如果想动态的切换活动关系，可以建立个辅助表，并利用这个辅助表中的类型生成切片器。然后使用度量值来判断切片器的选项，返回相应的计算：

在这里插入图片描述
在这里插入图片描述

4.3 CROSSFILTER

CROSSFILTER函数可临时修改两个表之间关系及交叉筛选方向，其语法为：

CROSSFILTER(<columnName1>, <columnName2>, <direction>)
  • columnName1 和 columnName2：这两个参数代表模型关系两端的列名，必须使用标准DAX语法和完全限定的列名。如果参数顺序相反，函数会自动交换它们。这两个参数不能是表达式。
  • direction：指定交叉筛选的方向，可以是：
    • None：此关系不会发生交叉筛选。
    • Both：双向筛选
    • OneWay：在一侧或关系查找端的筛选器会筛选另一侧，如果不清楚哪个是查找端，请用下面两个选项。
    • OneWay_LeftFiltersRight：columnName1 筛选 columnName2
    • OneWay_RightFiltersLeft：columnName2 筛选 columnName1
# powerbi # 数据库
Logo
2048 AI社区

有“AI”的1024 = 2048，欢迎大家加入2048 AI社区

更多推荐

UFW防火墙安全指南

UFW（Uncomplicated Firewall）是Ubuntu/Debian系统中简化防火墙管理的工具，通过直观命令帮助用户有效控制网络流量，提升系统安全性。文章详细介绍了UFW的基本命令，包括启停防火墙、添加规则、限制连接速率和日志配置等操作，并提供了安全最佳实践，如默认拒绝策略、IP地址限制和服务级规则管理。同时，还涵盖高级配置技巧，例如多网络接口设置、规则优先级调整、IPv6支持及与f

avatar 2048 AI社区

Java转AI大模型一文彻底搞懂大模型RAG

6年Java程序员，因公司做AI，所以转行AI大模型开发，刚学完RAG的基本概念和基本用法，给大家分享一下，让大家少走弯路

avatar 2048 AI社区
cover

API文档详解：结构、格式与最佳实践全解析

avatar 2048 AI社区