sql - 在 Power BI 中使用 DAX 进行左外连接(多对多关系)

Question

我将如何在 DAX 中进行左联接？当我尝试添加关系或使用左外连接 DAX 函数时，出现以下错误(见下文)。任何想法将不胜感激!

创建关系时出错:


尝试 NaturalLeftOuterJoin() 时出错


作为引用，我正在尝试创建损益表的计算行。
例子:

收入:100

费用:80

利润:20(收入-成本)

我的表如下:

Fact table:╔═══════════╦═════════╦═══════════╦════════╗║ YearMonth ║ StoreID ║ AccountID ║ Amount ║╠═══════════╬═════════╬═══════════╬════════╣║ 2017-01   ║ A       ║         1 ║    100 ║║ 2017-01   ║ B       ║         1 ║    200 ║║ 2017-01   ║ A       ║         2 ║    -50 ║║ 2017-01   ║ B       ║         2 ║    -50 ║║ 2017-02   ║ A       ║         1 ║     20 ║║ 2017-02   ║ B       ║         1 ║    150 ║║ 2017-02   ║ B       ║         2 ║    -20 ║╚═══════════╩═════════╩═══════════╩════════╝Template table:╔════════════╦═══════════╦═════════╗║ TemplateID ║ AccountID ║  Line   ║╠════════════╬═══════════╬═════════╣║        105 ║         1 ║ Revenue ║║        105 ║         2 ║ Cost    ║║        105 ║         1 ║ Profit  ║║        105 ║         2 ║ Profit  ║╚════════════╩═══════════╩═════════╝

In SQL this is super easy - I just do a left outer join on the AccountID field which creates records for the Profit line, like below:

 SELECT 
       f.[YearMonth]
      ,f.[StoreID]
      ,f.[AccountID]
      ,f.[Amount]
      ,t.[TemplateID]
      ,t.[AccountID]
      ,t.[Line]
  FROM [dbo].[Fact] f
  left join [dbo].[Templates] t
  on f.[AccountID] = t.[AccountID]

结果:

╔═══════════╦═════════╦═══════════╦════════╦════════════╦═══════════╦═════════╗
║ YearMonth ║ StoreID ║ AccountID ║ Amount ║ TemplateID ║ AccountID ║  Line   ║
╠═══════════╬═════════╬═══════════╬════════╬════════════╬═══════════╬═════════╣
║ 2017-01   ║ A       ║         1 ║    100 ║        105 ║         1 ║ Revenue ║
║ 2017-01   ║ B       ║         1 ║    200 ║        105 ║         1 ║ Revenue ║
║ 2017-02   ║ A       ║         1 ║     20 ║        105 ║         1 ║ Revenue ║
║ 2017-02   ║ B       ║         1 ║    150 ║        105 ║         1 ║ Revenue ║
║ 2017-01   ║ A       ║         2 ║    -50 ║        105 ║         2 ║ Cost    ║
║ 2017-01   ║ B       ║         2 ║    -50 ║        105 ║         2 ║ Cost    ║
║ 2017-02   ║ B       ║         2 ║    -20 ║        105 ║         2 ║ Cost    ║
║ 2017-01   ║ A       ║         1 ║    100 ║        105 ║         1 ║ Profit  ║
║ 2017-01   ║ B       ║         1 ║    200 ║        105 ║         1 ║ Profit  ║
║ 2017-02   ║ A       ║         1 ║     20 ║        105 ║         1 ║ Profit  ║
║ 2017-02   ║ B       ║         1 ║    150 ║        105 ║         1 ║ Profit  ║
║ 2017-01   ║ A       ║         2 ║    -50 ║        105 ║         2 ║ Profit  ║
║ 2017-01   ║ B       ║         2 ║    -50 ║        105 ║         2 ║ Profit  ║
║ 2017-02   ║ B       ║         2 ║    -20 ║        105 ║         2 ║ Profit  ║
╚═══════════╩═════════╩═══════════╩════════╩════════════╩═══════════╩═════════╝

然后我可以像这样旋转它:

╔═════════╦═════════╦═════════╗
║  Line   ║ Store A ║ Store B ║
╠═════════╬═════════╬═════════╣
║ Revenue ║     120 ║     350 ║
║ Cost    ║     -50 ║     -70 ║
║ Profit  ║      70 ║     280 ║
╚═════════╩═════════╩═════════╝

在 DAX 中，它似乎要复杂得多 - 希望有人能证明我错了!我读过双向过滤可能允许多对多关系，但我无法在这里工作。我尝试在 DAX 而不是 SQL 中执行此连接的原因是因为我有多个语句模板，并且如果可以通过 DAX 动态完成，则不希望多个加载具有非常相似的数据。谢谢!

Answer 1

Template有什么原因吗？需要表格，而不是作为计算的虚拟对象？因为仅从示例数据中我就看到事实表被不必要地复制了(7 -> 14 行)(也许我遗漏了一些关键点)。

如果没有，你可以简单地写几个Measures在 DAX 中进行 Power BI 中的计算(这正是 Power BI 的强大功能)，并且只有 Fact需要表。

DAX:

收入:

Revenue = 
CALCULATE(
    SUM('Fact'[Amount]),
    FILTER(
        'Fact',
        'Fact'[Amount] > 0
    )
)

成本:

Cost = 
CALCULATE(
    SUM('Fact'[Amount]),
    FILTER(
        'Fact',
        'Fact'[Amount] < 0
    )
)

利润:

Profit = [Revenue] + [Cost]

然后你可以使用 Matrix可视化以获得所需的结果:



附言如果您确实需要将收入/成本/利润放在行中而不是列中，则可能需要对数据进行透视或将计算写为新 Column (但不是 Measure )。这是由于 product limitation在 Power BI 中。