Forum Discussion
DAX: cyclic dependency detected
Greetings!
Friends, tell me how you can overcome the error "Cyclic dependency detected". I make two calculated columns that calculate the same value - but in different units. The first column counts the implementation in rubles, and the second column counts the implementation in weight units.
How can I avoid this error?
DAX below:
-- first DAX
Sales in rubles =
VAR CompanyID = 'Sales '[Company]
VAR ContractID = 'Sales '[Document]
VAR ContractDate = 'Sales '[Date_document]
RETURN
CALCULATE(SUM('Sales '[Sales_rub]),
FILTER(ALL('Full'[Company],'Full'[Document], 'Full'[Date_document], 'Full'[Date_approval]),
'Full'[Company] = CompanyID && 'Full'[Document] = ContractID && 'Full'[Date_document] = ContractDate && 'Full'[Date_document] >= 'Full'[Date_approval]
)
)
DAX: cyclic dependency detected
-- second DAX
Sales in kilograms =
VAR CompanyID = 'Sales '[Company]
VAR ContractID = 'Sales '[Document]
VAR ContractDate = 'Sales '[Date_document]
RETURN
CALCULATE(SUM('Sales '[Sales_kg]),
FILTER(ALL('Full'[Company],'Full'[Document], 'Full'[Date_document], 'Full'[Date_approval]),
'Full'[Company] = CompanyID && 'Full'[Document] = ContractID && 'Full'[Date_document] = ContractDate && 'Full'[Date_document] >= 'Full'[Date_approval]
)
)
Following up on my last post... Here's how to make PBI work with incremental refresh when using files: Chris Webb's BI Blog: Keep The Existing Data In Your Power BI Dataset And Add New Data To It Using Incremental Refresh Chris Webb's BI Blog (crossjoin.co.uk)
And here's the article that Chris refers to at the beginning of his article: Incremental refresh for files in a Folder or SharePoint - Power BI — Powered Solutions
13 Replies
Following up on my last post... Here's how to make PBI work with incremental refresh when using files: Chris Webb's BI Blog: Keep The Existing Data In Your Power BI Dataset And Add New Data To It Using Incremental Refresh Chris Webb's BI Blog (crossjoin.co.uk)
And here's the article that Chris refers to at the beginning of his article: Incremental refresh for files in a Folder or SharePoint - Power BI — Powered Solutions
Incremental updates as far as I understand are available on premium rates and the cloud, we have Desktop.
We plan to switch to the Report Server
ReyCarter , Try new columns like
Sales in rubles =
CALCULATE(SUM('Sales '[Sales_rub]),
FILTER('Full',
'Full'[Company] = earlier('Full'[Company]) && 'Full'[Document] = earlier('Sales '[Document]) && 'Full'[Date_document] = earlier('Sales '[Date_document])
&& 'Full'[Date_document] >= earlier('Full'[Date_approval])
)
)
Sales in kilograms =
CALCULATE(SUM('Sales '[Sales_kg]),
FILTER('Full',
'Full'[Company] = earlier('Full'[Company]) && 'Full'[Document] = earlier('Sales '[Document]) && 'Full'[Date_document] = earlier('Sales '[Date_document])
&& 'Full'[Date_document] >= earlier('Full'[Date_approval])
)
)Does not work
It is not possible to define a single value for the Company column in the Full table. This can happen if the measure formula refers to a column containing multiple values to get a single result, without specifying an aggregate, such as MIN, MAX...
It's a very, very, very bad idea to put CALCULATE in a calculated colum of a fact table. You'll feel the heat very soon. Fact tables are usually very big in terms of the number of rows. Forcing the engine to make millions (or many more) context transitions is nothing more than just asking for trouble with performance. And, of course, the use of CALCULATE is also the source of your troubles with cyclic references.
I really like these answers. What's the solution?
What is good or bad is useful. But it doesn't solve the problem..Please let me ask you this question: Why don't you use the power of Power Query to calculate these columns? In fact, this is the very place you should be going to in order to calculate such things, especially on fact tables. The calculation will not only be faster (which is important when refreshing the model). The column will also be compressed in an optimal way which means your DAX will be more performant.
