Forum Discussion
circular dependency error while sorting multiple records
I have an requirement to sort country wise currently data available :
Country Limits Type
US 20USD Insure
US 30 USD Secure
Canada 40 USD Pecure
Vietnam 80 USD Lecture
I have created calculated column and sort based on condition Canada=1,US=2,Vietnam=3
When i try to sort country column based on calculated column getting circular depedndency error since we have multiple values for US .
Output Expected in tables:
Country Limits Type
Canada 40 USD Pecure
US 20USD Insure
US 30 USD Secure
Vietnam 80 USD Lecture
Any helps appreciated!
karthik77700 - Apply the logic in Power Query through adding a "Custom Column":
This will load the data into the table already, and mean there is no circular dependency on the original column.
If I answered your question please mark my post as the solution, it helps others with the same challenge find the answer!
2 Replies
karthik77700 - Apply the logic in Power Query through adding a "Custom Column":
This will load the data into the table already, and mean there is no circular dependency on the original column.
If I answered your question please mark my post as the solution, it helps others with the same challenge find the answer!
You're encountering a circular dependency error because you're trying to sort the Country column using a calculated column that depends on Country itself, which Power BI doesn't allow when the relationship isn't one-to-one (multiple rows for "US").
To avoid this, you can create a separate table that defines the custom sort order and then use it to sort your main table.
1. Create a Sort Table
Go to Modeling > New Table and enter:
CountrySortOrder = DATATABLE(
"Country", STRING,
"SortOrder", INTEGER,
{
{"Canada", 1},
{"US", 2},
{"Vietnam", 3}
}
)
2. Create a RelationshipGo to Model view.
Create a relationship between YourMainTable[Country] and CountrySortOrder[Country].3. Sort the Country Column
In your main table, select the Country column.
Click "Sort by Column" and choose CountrySortOrder[SortOrder].This will allow Power BI to sort the Country column without circular dependency, even if there are multiple rows per country.
Please give a thumbs up if this fixes your issue.
