SELECTCOLUMNS function

[mp390988]

Hi,

Is it not possible to include a calculated column from another table in the SELECTCOLUMNS function?

So for example, in table A I have a calculated column,

Then I am creating a new table using the SELECTCOLUMNS function where I am trying to reference the calculated column from table A but it is giving me an error saying circular reference.

[AmiraBedh · Accepted solution]

Hello !

Your circular reference error means the new calculated table you’re building depends on Table A[Calculated column] and directly or indirectly that calculated column or something it uses depends back on the new table (or on something that depends on it). DAX won’t allow cycles in the dependency graph at refresh time.

My assumptions :

• Table A[Calculated column] uses LOOKUPVALUE, RELATED, RELATEDTABLE, or a something that reads from the new table or a table built from it

• You add a relationship and then a calc column on A that uses RELATED(NewTable[…]) while NewTable is built from A → A ↔ NewTable loop.

• A measure inside the calc column references NewTable or virtual tables that rely on NewTable.

If you share your code I can help you.

[Shahid12523 · Accepted solution]

You can't use a calculated column from another table in SELECTCOLUMNS if it creates a circular dependency. Instead, use a measure, move the logic to Power Query, or redesign the model to avoid indirect references. Measures are safer because they don’t rely on row context.

[mp390988]

Thank you all for responding, it has been helpful.

[FBergamaschi]

The answer depends onthe code you are writing, we cannot answer in general as in principle you can do it

If you can share the code and a small sibset of the rows of the tables involved, we can help you

If this helped, please consider giving kudos and mark as a solution

@me in replies or I'll lose your thread

Want to check your DAX skills? Answer my biweekly DAX challenges on the kubisco Linkedin page

Consider voting this Power BI idea

Francesco Bergamaschi

MBA, M.Eng, M.Econ, Professor of BI

[HarishKM · Accepted solution]

@mp390988 Hey,
 
In DAX, when using the SELECTCOLUMNS function to create a new table, directly referencing a calculated column from another table can result in a circular reference error if the calculated column itself depends on the data in the new table or vice versa. This happens because DAX evaluates dependencies to ensure data integrity and avoids situations where a calculation could reference itself indirectly, causing an infinite loop.

To resolve this, ensure that the calculated column in the original table is independent of the new table or consider using measures or variables to avoid direct dependencies.

Thanks

Harish M

Give kudos if this solution resolves the issue.
Accept it as the solution if it helps.

[Shahid12523 · Accepted solution]

You can't use a calculated column from another table in SELECTCOLUMNS if it creates a circular dependency. Instead, use a measure, move the logic to Power Query, or redesign the model to avoid indirect references. Measures are safer because they don’t rely on row context.

[AmiraBedh · Accepted solution]

Hello !

Your circular reference error means the new calculated table you’re building depends on Table A[Calculated column] and directly or indirectly that calculated column or something it uses depends back on the new table (or on something that depends on it). DAX won’t allow cycles in the dependency graph at refresh time.

My assumptions :

• Table A[Calculated column] uses LOOKUPVALUE, RELATED, RELATEDTABLE, or a something that reads from the new table or a table built from it

• You add a relationship and then a calc column on A that uses RELATED(NewTable[…]) while NewTable is built from A → A ↔ NewTable loop.

• A measure inside the calc column references NewTable or virtual tables that rely on NewTable.

If you share your code I can help you.

[alish_b]

Hello @mp390988 ,

No, Power BI will not allow to directly reference a calculated column in the definition of another table using SELECTCOLUMNS. This is mainly because of the fact that when Power BI refreshes a table it will compute the rows step by step, and so if the columns are not ready in the original table, they cannot be referenced in the secondary table. The easiest workaround will be to recreate the logic in ADDCOLUMNS or SELECTCOLUMNS itself. 

Hope it helps!