Forum Discussion
Advocate IRow Identifier within Power BI for Desktop (Circular dependencies with the DAX CALCULATE function)
Memorable MemberThis is a working in Tabular and Visual Studio SSDT..also in PowerPivot advanced table properties..not yet implemented in PBI Desktop..You can try the latest
For example, using ALLEXCEPT to remove the calculated columns from the set of columns that become part of the dependency list is a viable option, but it makes all formulas more complicated. On the other hand, using ALLEXCEPT might be useful for very big tables, where the addition of a row identifier would cause memory footprint to grow too much.
The second calculated column will be like = CALCULATE ( your measure ( and filters ) ; ALLEXCEPT ( TableName; TableName[FirstCalculatedColumn])
Never tried it but probably will work ( since Alberto says it will )
Community AdminIf memory footprint isn't an issue for you, there's also an 'Add Index Column' transformation that you could use in the Query Editor. That would be a quick way to add an index that you can use as a Row Identifier.
Please mark either this post or Konstantinos' answer as a solution so we know the problem's solved!
thanks!
Brian_MContinued Contributor
Hi WillT / konstantinos,
I realise this is an old post. Just wondering whether or not Power BI Desktop has received the functionality to set a column as a row identifier yet? I've read the helpful sqlbi posts above and understand the alternative approaches, just wondering whether the feature has made its way from tabular to PBI Desktop.
Many thanks!
- WillT
Hi Brian. No, it's not completed yet. It's on our to-do list but it's a relatively little-known feature and we've been prioritizing things requested by more folks. Did you try the index column workaround?
- Brian_M
Hi WillT I did try the row ID trick but no luck for my scenario.
Although I've gotten by the issue by defining a measure instead of a calculated column, I thought I'd spell it all out here in case you have any insight.
I am trying to add a calculated column which is in the form Column = CALCULATE ( ....) which I understand from Alberto's post:
"The problem is that any calculated column containing CALCULATE (or a call to any measure, which adds an automatic CALCULATE) creates a dependency from all of the columns of the table."
As a precaustion I deleted all other calculated columns on that table but it didn't help.
The reason I was trying to set it as a calculated column was to bypass a performance issue (30 seconds to calculate as a measure) - i.e.just to calculate it once at report initiation. I've managed to tune the formula such that it runs in 1 second (instead of 30) - ... thanks to Marco for his Optimising DAX Course in London!! so I'm now able to run it as a measure for now.
I suspect I know why I am getting the circular dependency when I try to set it as a calculated column, but I can't for the life of me figure out how to get by it. I think it might be because of the variables I'm using inside the SUMX to materialise certain data early to reduce the number of rows being crunched by my iterators. I think I've isolated the problem to the line that is marked -----YYYY in the code below. I couldn't figure out how to use Alberto's ALLSELECTED work around, and the row ID's didn't work.
It runs in DAX Studio just fine as an ADDCOLUMNS(....) but when I try to add it as a calculated column on my table I get
"A circular dependency was detected: Payments[Column]"
Here's the code. I gave it >4 hours of my life trying to make it work!
Column = CALCULATE( SUMX ( Payments, /*-----YYYYY Think the problem is here */ VAR MyCurrency = CALCULATE ( VALUES ( Currency[isocurrencycode] ) ) VAR MyDate = Payments[Payment Date] VAR FilteredXrateTable = TOPN ( 1, FILTER ( CALCULATETABLE ( Xrates, Xrates[From] = MyCurrency ), Xrates[Valid from] <= MyDate ), Xrates[Valid from], DESC ) RETURN Payments[Gross Local] * CALCULATETABLE ( VALUES ( Xrates[Exch. Rate] ), FilteredXrateTable ) )