Power BI为什么检测到循环依赖 [英] Power BI why circular dependency is detected

问题描述

当我尝试在维度#product（或#region）与作为产品x区域的笛卡尔坐标的#bridge表之间创建关系时，您能否解释为什么会遇到此循环依赖警报消息？

我已将#bridge与Sales and Budget通过单列P @ G连接起来，是产品和地区的串联。

下载此处的文件：

解决方案

解决方案很简单。不要在DAX网桥表中使用CALCULATE函数。而是将所有这些列与计算列稍后添加到同一表中。

我更改了桥接表的原始代码：

  ADDCOLUMNS（
 CROSSJOIN（'#product'，'#region'），
 P @ R，COMBINEVALUES（ @，'＃product'[product]，'#region'[region ]），
 sales，CALCULATE（SUM（Budget [target]））），
 IsSale，IF（CALCULATE（SUM（Budget [target]））> 0， Yes，  No）
）
  

简单一些：

  ADDCOLUMNS（
 CROSSJOIN（'#prodact'，'#region'），
 P @ R，COMBINEVALUES（ @ ，'＃prodact'[产品]，'＃region'[区域]）
）
  

我修改了桥表的DAX代码，以便仅保留连接所需的列。我需要计算的列已添加为计算列。就是这样。

对于玩桥牌桌，我推荐阿尔贝托·法拉利（Alberto Ferrari）的文章： https://www.sqlbi.com/articles/avoiding-circular-dependency-errors-in- dax / 。它启发了我解决问题。我从Alberto的书中得到的是VALUES和ALL函数对桥表不利。他提到在桥DAX表中使用CALCULATE函数的问题。该函数以某种方式转换为ALL和FILTER函数的混合。代替VALUE和ALL，使用DINSTINCT和ALLNOBLANKROW函数。

工作 PBIX 文件。哇！

Can you please explain why I run into this alert message of circular dependency when I try to create relationship between dimension #product (or #region) and a #bridge table which is a Cartesian of product x region?

I have connected #bridge with Sales and Budget by single column P@G witch is concatenation of product and region.

Download file here: PBIX

解决方案

The solution is simple. Do not use CALCULATE function in the DAX bridge tables. Instead add all that columns to the same table later as calculated columns.

I changed the original code of the bridge table which was:

ADDCOLUMNS (
    CROSSJOIN ( '#product', '#region' ),
    "P@R", COMBINEVALUES("@",'#product'[product], '#region'[region]),
    "sales", CALCULATE ( SUM ( Budget[target] ) ),
    "IsSale", IF ( CALCULATE ( SUM ( Budget[target] ) ) > 0, "Yes", "No" )
)

To something simpler:

ADDCOLUMNS (
    CROSSJOIN ( '#prodact', '#region' ),
    "P@R", COMBINEVALUES("@",'#prodact'[product], '#region'[region])
)

I modified the DAX code of bridge table so as to leaving only the columns necessary for joins. The columns that I needed to be calculated I added as calculated columns. And that's it. It was by pure chance I found that out while experimenting with it.

For playing with bridge tables I recommend this Alberto Ferrari's article: https://www.sqlbi.com/articles/avoiding-circular-dependency-errors-in-dax/. It inspired me to solve the problem. What I get from the Alberto's text is that the functions VALUES and ALL are no good for bridge tables. He mentions issue of using CALCULATE function inside the bridge DAX tables. The function somehow is translated to mixture of ALL and FILTER functions. Instead of VALUE and ALL, use functions as DINSTINCT and ALLNOBLANKROW.

Working PBIX file. Hurray!

这篇关于Power BI为什么检测到循环依赖的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！