如何将含双分组子查询内连接的T-SQL转换为DAX？

表结构说明 #

• Budgets表：
  
• Transactions表：
  

原T-SQL语句 #

SELECT Budget.[CUSTOMER_ID],[Budget_Year],[Budget_Month]
    ,[Budget_Calculation],Transactions.[Total Cost Value Calculation]  
FROM (
    SELECT [Budgets].[CUSTOMER_ID] 
        ,[FinYear] [Budget_Year]
        ,[Month] [Budget_Month]
        ,sum([Budget]) [Budget_Calculation] 
    FROM [SQLTestScripts].[dbo].[Budgets]  
    group by [Budgets].[CUSTOMER_ID],[FinYear],[Month], budget
    ) Budget
INNER JOIN (
    SELECT 
        [CUSTOMER_ID]
        ,datepart(year,[Transactions].[TRANSACTION_DATE]) [Transactions_Year]
        ,datepart(month,[Transactions].[TRANSACTION_DATE]) [Transactions_Month]
        ,SUM([COST_VALUE]*QTY_INVOICED )[Total Cost Value Calculation]
    FROM [dbo].[Transactions]
    group by 
        [Transactions].[TRANSACTION_DATE],
        [CUSTOMER_ID]
    ) Transactions 
on Budget.CUSTOMER_ID = Transactions.CUSTOMER_ID
and Budget.[Budget_Year] = Transactions.[Transactions_Year] 

问题描述 #

如何将上述T-SQL语句转换为DAX语句？我尝试了以下DAX语句，但未得到正确结果：

EVALUATE
SUMMARIZE(
    FILTER(NATURALLEFTOUTERJOIN(Budget,Transactions)
        ,Budget[CUSTOMER_ID] = Transactions[CUSTOMER_ID] 
        && Budget[FinYear] = YEAR(Transactions[TRANSACTION_DATE]) 
        ,Budget[CUSTOMER_ID],Budget[FinYear],Budget[Month]
        ,"Budget Calculation",sum(Budget[Budget]) 
        ,"Total Cost Value Calculation",sum(Transactions[Total Cost Value])
    )
)

正确DAX实现 #

原T-SQL的核心逻辑是先分别聚合两个表，再按客户ID和年份做内连接，你写的DAX存在两个关键问题：一是SUMMARIZE语法错误，筛选条件与分组字段的位置混乱；二是直接对原始表做连接后再聚合，不符合原SQL先聚合再关联的逻辑。

正确的DAX实现如下：

EVALUATE
VAR AggregatedBudgets =
    SUMMARIZE(
        Budgets,
        Budgets[CUSTOMER_ID],
        Budgets[FinYear],
        Budgets[Month],
        "Budget_Calculation", SUM(Budgets[Budget])
    )
VAR AggregatedTransactions =
    SUMMARIZE(
        Transactions,
        Transactions[CUSTOMER_ID],
        "Transactions_Year", YEAR(Transactions[TRANSACTION_DATE]),
        "Total Cost Value Calculation", SUM(Transactions[COST_VALUE] * Transactions[QTY_INVOICED])
    )
RETURN
    NATURALINNERJOIN(
        AggregatedBudgets,
        SELECTCOLUMNS(
            AggregatedTransactions,
            "CUSTOMER_ID", AggregatedTransactions[CUSTOMER_ID],
            "FinYear", AggregatedTransactions[Transactions_Year],
            "Total Cost Value Calculation", AggregatedTransactions[Total Cost Value Calculation]
        )
    )

逻辑说明 #

1. 用VAR定义两个聚合表，完全对应T-SQL中的两个子查询逻辑
2. 通过SELECTCOLUMNS调整Transactions聚合表的字段名，让关联字段（年份）与Budgets聚合表的字段名匹配，确保NATURALINNERJOIN能自动按相同字段完成关联
3. 该写法完全贴合原SQL的执行逻辑，避免了原始表直接连接带来的性能和结果准确性问题

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