Forum Discussion
DAX: cyclic dependency detected
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
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.
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?
