如何将带投影的多表传入DAX的SUMMARIZE/SUMMARIZECOLUMNS？

背景说明 #

现有模型包含两张已建立关联的表：

• Sales表：包含Amount列、日期时间类型的Sold列
• Products表：包含日期时间类型的IntroducedToMarket列
  两表通过ProductId（Sales）与Id（Products）建立关系，需实现与以下SQL逻辑完全等价的DAX代码：

SELECT YearSold, YearIntroducedToMarket, SUM(Amount) FROM 
(SELECT *, Year(Sold) as YearSold from Sales) S
inner join 
(Select *, Year(IntroducedToMarket) as YearIntroducedToMarket FROM Products) P ON P.Id = S.ProductId
GROUP BY YearSold, YearIntroducedToMarket

你已通过ADDCOLUMNS生成了年份投影表达式：

ADDCOLUMNS(Sales, "YearSold", Year(Sold))
ADDCOLUMNS(Products, "YearIntroducedToMarket", Year(IntroducedToMarket))

以下是将这些表表达式传入SUMMARIZE或SUMMARIZECOLUMNS的具体实现，同时支持扩展使用两表及其他表的其他列：

方案1：使用SUMMARIZE结合NATURALINNERJOIN #

通过NATURALINNERJOIN关联两个ADDCOLUMNS生成的临时表，再用SUMMARIZE完成分组聚合：

SUMMARIZE (
    -- 关联添加了年份列的Sales和Products表
    NATURALINNERJOIN (
        ADDCOLUMNS ( Sales, "YearSold", YEAR ( Sales[Sold] ) ),
        ADDCOLUMNS ( Products, "YearIntroducedToMarket", YEAR ( Products[IntroducedToMarket] ) )
    ),
    -- 指定分组列
    [YearSold],
    [YearIntroducedToMarket],
    -- 定义聚合计算项
    "Sum(Amount)", SUM ( [Amount] )
)

扩展其他列示例 #

若需要加入产品名称、客户ID等其他列，只需在分组列列表中追加对应字段即可：

SUMMARIZE (
    NATURALINNERJOIN (
        ADDCOLUMNS ( Sales, "YearSold", YEAR ( Sales[Sold] ) ),
        ADDCOLUMNS ( Products, "YearIntroducedToMarket", YEAR ( Products[IntroducedToMarket] ) )
    ),
    [YearSold],
    [YearIntroducedToMarket],
    Products[Name], -- 新增产品名称列
    Sales[CustomerId], -- 新增客户ID列
    "Sum(Amount)", SUM ( [Amount] )
)

方案2：使用SUMMARIZECOLUMNS（简洁推荐） #

SUMMARIZECOLUMNS可直接利用模型已有的表关系，无需手动关联表，写法更简洁高效：

SUMMARIZECOLUMNS (
    -- 定义分组列并指定别名
    YEAR ( Sales[Sold] ) AS YearSold,
    YEAR ( Products[IntroducedToMarket] ) AS YearIntroducedToMarket,
    -- 聚合计算
    "Sum(Amount)", SUM ( Sales[Amount] )
)

扩展其他列示例 #

添加其他列只需在分组列列表中追加对应的字段即可：

SUMMARIZECOLUMNS (
    YEAR ( Sales[Sold] ) AS YearSold,
    YEAR ( Products[IntroducedToMarket] ) AS YearIntroducedToMarket,
    Products[Name], -- 新增产品名称
    Sales[CustomerId], -- 新增客户ID
    "Sum(Amount)", SUM ( Sales[Amount] )
)

示例数据与验证结果 #

创建示例数据的SQL脚本 #

create table Products (Id int, Name nvarchar(100), IntroducedToMarket datetime2)
create table Sales (Id int, ProductId int, CustomerId int, Sold datetime2, Amount float)

insert into Products VALUES(1,  'Product 1',    '2025-01-01')
insert into Products VALUES(2,  'Product 2',    '2024-01-01')
insert into Products VALUES(3,  'Product 3',    '2023-01-01')
insert into Products VALUES(4,  'Product 4',    '2024-07-01')

insert into Sales VALUES(1, 1,  1,  '2025-03-02',   10)
insert into Sales VALUES(2, 1,  2,  '2025-05-03',   20)
insert into Sales VALUES(3, 2,  1,  '2025-01-30',   30)
insert into Sales VALUES(4, 2,  2,  '2024-11-11',   40)
insert into Sales VALUES(5, 3,  1,  '2025-02-20',   50)
insert into Sales VALUES(6, 3,  1,  '2024-10-07',   60)
insert into Sales VALUES(7, 3,  2,  '2023-07-24',   70)
insert into Sales VALUES(8, 4,  1,  '2024-09-19',   80)
insert into Sales VALUES(9, 4,  1,  '2025-02-13',   90)

查询结果 #

YearSold    YearIntroducedToMarket  Sum(Amount)
2023        2023                    70
2024        2023                    60
2025        2023                    50
2024        2024                    120
2025        2024                    120
2025        2025                    30

内容的提问来源于stack exchange，提问作者Stephan Hoffmann