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

问题描述

您能否解释一下为什么当我尝试在维度 #product(或 #region)和作为产品 x 区域的笛卡尔坐标的 #bridge 表之间创建关系时遇到此循环依赖的警告消息?

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

在此处下载文件:

解决方案

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

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

添加列 (交叉连接('#product'，'#region')，"P@R", COMBINEVALUES("@",'#product'[product], '#region'[region]),销售额"，计算(总和(预算[目标]))，"IsSale", IF (CALCULATE (SUM (Budget[target])) > 0, "Yes", "No"))

更简单的:

添加列 (交叉连接('#prodact'，'#region')，"P@R", COMBINEVALUES("@",'#prodact'[产品], '#region'[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屋！