GENERATE 函数对 Table1 的每一行计算 Table2,返回 Table1 中每一行与 Table2 的相应行之间生成的笛卡尔积。GENERATE 使用两个表表达式:
GENERATE
GENERATE ( <Table1>, <Table2> )
参数 | 属性 | 描述 |
---|---|---|
Table1 | GENERATE 使用的基础表. | |
Table2 | 在 Table1 的每一行计值的表表达式 |
GENERATEALL
GENERATEALL(<Table1>, <Table2>)
参数 | 属性 | 描述 |
---|---|---|
Table1 | GENERATEALL 使用的基础表. | |
Table2 | 在 Table1 的每一行计值的表表达式 |
GENERATEALL 与 GENERATE 的计值过程相同,仅在对空行的处理上有所区别。当对<Table2>的计算返回空记录时,GENERATEALL 将来自<Table1>的当前行包含在结果中。这与 GENERATE 不同,后者移除所有空值对应的<Table1>当前行。
另外,与 CROSSJOIN 类似,GENERATE 系列函数的 Table1 和 Table2 中的所有列名不能有重复,否则将报错。
示例用法
GENERATE 的一个简单用法是生成包含产品类别和子类别的所有有效组合的表,你可以使用这个查询:
EVALUATE GENERATE ( 'Product Category', RELATEDTABLE ( 'Product Subcategory' ) )
GENERATE 调用的 Table2 通常包含 RELATEDTABLE 或 CALCULATETABLE 函数,以便使用 Table1 迭代的行上下文进行上下文转换。
如果在第二参数中省略 RELATEDTABLE,将得到与 CROSSJOIN 相同的结果,因为没有通过 RELATEDTABLE 将行上下文转换为筛选上下文,(RELATEDTABLE 是 CALCULATEDTABLE 简化版的别名)
实际上,以下查询是等效的:
EVALUATE GENERATE( 'Product Category', 'Product Subcategory' ) EVALUATE CROSSJOIN( 'Product Category', 'Product Subcategory' )
GENERATE 的一个更有趣的例子是如何得到一个表,使其包含每年销售量排名前两位的产品。以下查询对每一年执行 TOPN 函数,计算每一年中对应产品的排名:
EVALUATE GENERATE ( VALUES ( 'Date'[Calendar Year] ), TOPN ( 2, SUMMARIZE ( RELATEDTABLE ( Sales ), Product[Product Name] ), CALCULATE ( SUM ( Sales[Quantity] ) ) ) )
这里需要注意的是,查询只返回那些至少包含一笔销售记录的年份。如果你还希望在销售表中包含没有任何销售记录的年份,可以使用 GENERATEALL 代替 GENERATE,如以下查询所示:
EVALUATE GENERATEALL ( VALUES ( 'Date'[Calendar Year] ), TOPN ( 2, SUMMARIZE ( RELATEDTABLE ( Sales ), Product[Product Name] ), CALCULATE ( SUM ( Sales[Quantity] ) ) ) )
如果你习惯使用 SQL,你可以认为 GENERATE 函数类似于 SQL 中的 CROSS APPLY 条件,而 GENERATEALL 函数类似于 SQL 中的 OUTER APPLY 条件
老师你好,在这个例子中:
EVALUATE
GENERATE (
VALUES ( ‘Date'[Calendar Year] ),
TOPN (
2,
SUMMARIZE (
RELATEDTABLE ( Sales ),
Product[Product Name]
),
CALCULATE (
SUM ( Sales[Quantity] )
)
)
)
在pbi desktop中,为什么我把RELATEDTABLE去掉了,返回的结果不是每年和(综合所有年份)最高销售量的两个产品的笛卡尔乘积,而返回了很多行数据,貌似是年份与所有产品的笛卡尔积,为什么呢?
老师,您好。如果此度量值的外部切片器为产品名称,
共同客户数量 =
VAR t1 =
GENERATE(
VALUES(‘订单表'[客户ID]),
EXCEPT(
VALUES(‘产品表'[产品名称]),
CALCULATETABLE(VALUES(‘产品表'[产品名称]))))
VAR t2 = SUMMARIZE(t1,’订单表'[客户ID])
VAR t3 = EXCEPT(VALUES(‘订单表'[客户ID]),t2)
RETURN COUNTROWS(t3)
度量的计值顺序是这样吗:
1. Generate的第一参被外部切片器筛选,形成所选产品的的不重复的客户ID表;
2. Except的第一参数也被透视表切片器筛选,形成切片器所选产品的产品名称仅1列的表;
3. Except的第二参数cal将上面第1步骤的表进行行迭代,以 Generate的第一参的第一行为例,转换为筛选上下文后,这时内部筛选器为客户ID,外部切片器为产品名称,形成 Except的第二参数的表,仅有产品名称列的表;
4. Except取第一参数表跟没和第二参数做交集的表;
5. Generate的第一参的表和第4步骤的表进行cross函数功能;
6.循环1-5步骤。
老师,哪儿理解的有问题的话,麻烦指正。谢谢。
高老师,如下图上,我的利润表,要把类似比率的科目带上。但是我的源数据里没有现成的。需要怎么操作哇?我知道用Divide函数写度量值。但是,显示起来就不是想要的样子了。
其实这个generate还是稍微有点抽象了,我看到那个取年度前二的例子才有一种醒悟的感觉,倒不是理解了,反倒是因为这个案例的目的,生成每年度的前二,目前我用别的函数还暂时无法写不出来,所以觉得这个函数在这方面有奇效?第一个包含产品类别和产品子类的有效组合好像summerize可以实现?对于取每年度前2这个例子,个人的理解是这个函数比较强大的地方是,topn这个函数返回的2列的表是和年度没有关系的,但是作为第二参数,直接交给generate,此时generate可以按照第一个表参数去重新计算第二个表参数topn,从而达到目的?不知道我的理解是不是有偏差。在学这个函数的时候脑袋里总会浮现出group by的身影,类比一下group by的等价形式(对数据进行二次聚合):
VAR table2 =
ADDCOLUMNS (
VALUES ( ‘追加'[劳务队名称] ),
“平均”, CALCULATE (
AVERAGEX (
ADDCOLUMNS (
SUMMARIZE ( ‘追加’, ‘追加'[劳务队名称], ‘日期表'[年份] ),
“金额”, CALCULATE ( MAX ( ‘追加'[计价金额] ) )
),
[金额]
)
)
)
在这个例子里,用summerize和max生成的值,里面包含了外层第二次聚合的 劳务队名称,所以外层的values(劳务队名称)迭代函数可以求得结果。
但是generate最里面一层没有年份的维度,只是用到了relatedtable,外部第一个表表参数直接把values(年份)作为第一表参数就可以得到想要的结果。
虽然generate第二个参数只能是表,转换不了group by的等价形式,不过还是希望能帮忙解答一下这两种写法的联系和区别。作为初学者我不知道怎么就把这两个不太相关的东西放在一起了,麻烦解答一下疑惑,谢谢!