Row Identifier within Power BI for Desktop (Circular dependencies with the DAX CALCULATE function)

[pftcg]

I'm using Power BI for Desktop and I'm using the CALCULATE function. After attempting to create another Column using Calculate I get a "circular dependency" error.

Doing some research on my own I found the following: https://www.sqlbi.com/articles/understanding-circular-dependencies/

Is there a way to accomplish the following within PowerBI for Desktop?

"2. You can manually set the property of Row Identifier for ProductKey to TRUE using the Properties window inside SSDT or the corresponding feature in PowerPivot"

Thanks!

[geraldcousi]

I'm wondering if the "row identifier" feature is now available in PowerBI Desktop ?

I've worked around the circular dependencies within Power Pivot, and it's also fine in PowerBI desktop after importing data model from Power Pivot.

But is there any option to directly manage the property directly within PowerBI desktop ? As far as I would like to use that property for an already existing daya model, created only with PowerBI desktop.

Regards.

[konstantinos]

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

[lsutrey]

Hi Konstantinos,

Your suggestion worked, but you actually have to apply the ALLEXCEPT filter to all of the calculated columns, not just the 2nd column.  Once I did it to both of mine, the error went away.  I presume if I do a 3rd calculated column, I'll have to update the first two as well.  Cumbersome, but at least it works.  Thanks for your suggestion!

Trey Cook

[WillT]

If 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_M]

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