Forum Discussion

pftcg's avatar
pftcg
Icon for Advocate I rankAdvocate I
11 years ago

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

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...
Need Help
konstantinos's avatar
konstantinos
Icon for Memorable Member rankMemorable Member
11 years ago

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 )

  • WillT's avatar
    WillT
    Icon for Community Admin rankCommunity Admin
    10 years ago

    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's avatar
      Brian_M
      Icon for Continued Contributor rankContinued Contributor
      9 years ago

      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's avatar
        WillT
        Icon for Community Admin rankCommunity Admin
        9 years ago

        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?

  • lsutrey's avatar
    lsutrey
    New Member
    9 years ago

    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