circular dependency error while sorting multiple records

[karthik77700]

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!

[mark_endicott · Accepted solution]

@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!

[wardy912]

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 Relationship

Go 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.