在PowerBI中使用DAX进行左外连接（多对多关系）

Question

在PowerBI中使用DAX进行左外连接（多对多关系）

sqljoinbusiness-intelligencepowerbidax

5

我该如何在DAX中进行左连接？当我尝试添加关系或使用左外连接DAX函数时，我会收到以下错误（见下文）。有什么想法将不胜感激！创建关系时出错：

You can't create a relationship between these two columns becasue one of the columns must have unique values.

尝试NaturalLeftOuterJoin（）时出错：

No common join columns detected. The join function 'NATURALLEFTOUTERJOIN' requires at-least one common join column.

参考资料，我正在尝试创建计算行的损益表。例如：

营业额：100 成本：80 利润：20（营业额-成本）

我的表格如下：

事实表： ╔═══════════╦═════════╦═══════════╦════════╗ ║ 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 ║ ╚═══════════╩═════════╩═══════════╩════════╝

模板表： ╔════════════╦═══════════╦═════════╗ ║ TemplateID ║ AccountID ║ Line ║ ╠════════════╬═══════════╬═════════╣ ║ 105 ║ 1 ║ Revenue ║ ║ 105 ║ 2 ║ Cost ║ ║ 105 ║ 1 ║ Profit ║ ║ 105 ║ 2 ║ Profit ║ ╚════════════╩═══════════╩═════════╝

在SQL中，这非常容易 - 我只需要在AccountID字段上执行左外连接，就可以创建利润行的记录，如下所示：

 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动态完成而不必加载非常相似的数据。谢谢！

- FirstRedPepper

你尝试过使用NATURALINNERJOIN(<leftJoinTable>, <rightJoinTable>)吗？https://msdn.microsoft.com/zh-cn/library/dn802543.aspx?f=255&MSPPError=-2147217396 - Horaciux

另外，请查看此帖子 https://www.sqlbi.com/articles/from-sql-to-dax-joining-tables/ - Horaciux

感谢Horaciux，NaturalInnerJoin将不会给出所需的结果（真的需要左连接），话虽如此，它仍然产生了相同的错误。我看了链接，但它没有太多意义 - 希望有人可以解决这种情况。谢谢你的建议！！：） - FirstRedPepper

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Foxan Ng · Accepted Answer

有没有任何理由需要 Template 表，除了作为计算的虚拟表？因为仅从示例数据中我看到事实表被不必要地复制了（7 -> 14行）（也许我错过了一些关键点）。

如果没有，您可以在 Power BI 中编写几个 Measures 的 DAX 来进行计算（这正是 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]

然后您可以使用“矩阵”可视化来获得所需的结果：

P.S. 如果你真的需要将收入/成本/利润放在行而不是列中，你可能需要将数据进行透视或编写新的“列”计算（但不是“度量”）。这是由于Power BI中的产品限制所致。