cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ReyCarter
Frequent Visitor

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]
)
)

1 ACCEPTED SOLUTION
daxer
Solution Sage
Solution Sage

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 I...

 

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 

View solution in original post

13 REPLIES 13
daxer-almighty
Solution Sage
Solution Sage

@ReyCarter

 

No. It does NOT require a date field. Please check out the links I posted.

daxer
Solution Sage
Solution Sage

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 I...

 

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 

View solution in original post

ReyCarter
Frequent Visitor

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

daxer
Solution Sage
Solution Sage

@ReyCarter 

 

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.

ReyCarter
Frequent Visitor

I really like these answers. What's the solution?
What is good or bad is useful. But it doesn't solve the problem..

@ReyCarter 

 

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.

ReyCarter
Frequent Visitor

We have fact tables divided by year into different tables (2020,2021). Only the 2021 table is updated with new data.
In powerbi, we have a fact table formed by the formula UNION(2020,2021).
Power Query is not clear how to use it here, since there is no general table in it, there are only two tables for the years 2020,2021 - how to calculate, for example, a column for a fact table that is not physically present in PQ?

@ReyCarter 

 

I can see you are doing things the wrong way all along. If you have a model where tables are in an Import mode (and I assume you do), then all the tables are imported through Power Query. You should do all our ETL in Power Query, not in DAX. UNION on 2 fact tables in DAX? Very bad idea. Please do all your data preparation in Power Query. This is the right and correct way. Power Query is a data mashup engine for a reason... DAX is for Data Analysis (Data Analysis eXpressions). Please note that if you don't program your PBI correctly from the start, you'll be having huge problems later down the line. Trust me: You don't want to do things incorrectly.

ReyCarter
Frequent Visitor

OK, how do you load data from 500 Excel files into Power Query with the same format? You will update the report for 3 hours when a new file appears or a single file in a folder is updated.
We have a new Excel file in the folder and the division into several tables by year is made so that the old data is not updated, but only the data for 2021 (current) is updated.
When analyzing data, we work with a common table (including all periods). If you have a better solution, tell me - how can I do this task correctly?

@ReyCarter 

 

I've created 500 Excel files in a folder on my PC. In each there is a table with 20 columns (a mix of integers, floats and text) and 365 rows. I used the Folder connector in Power Query to load this data, automatically consolidate into one table and then load into PBI Desktop. The process of fully refreshing all data takes not more than about 10 seconds.

 

So, the question is: How many rows do you have in each of the files that you are worried about the refresh time? I'm not sure how to do an incremental refresh when loading data from Excel files and if it's possible at all. I'll have to find out. But if you don't have monstrous Excel files with tens of thousands of rows, then I can't see why you couldn't do what I've done...

ReyCarter
Frequent Visitor

The fact is that we have 500 files and each has 10-15 thousand lines. It takes us 3 hours. I say this not because I'm making it up, but as it is in practice.
10 thousand is not monstrous files, monstrous is 800 thousand and millions.

Or even on a few tabs like to do.I'll look at your link about incremental updates - but as far as I understand it requires a field with the date of addition - we do not have it.
More precisely, there is but it does not match the current one.

Therefore, it was decided to do it in the only possible way.

amitchandak
Super User IV
Super User IV

@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])
)
)



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

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...

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors