Circular error when using a calculated index column for sorting

Power BI
1

I have a column that I need to sort in a specific order.

I created a calculated column for indexing with the following Dax code.

DepartmentID =
VAR indexNo = ‘Account Schedule’[Dept.]
Return
Switch(indexNo,
“New”, 1,
“Used”,2,
“Systems”,3,
“Service”,4,
“Parts”,5,
“Rental”,6,
“Delivery”,7
)

When I go to the column that I want sorted, I select the column and then click on the sort by column icon and select the new calculated column “Departmentid”. I immediately get a circular reference error. What am I missing.?

calculatedcol Deptcol

2

HI @lomorris. This bit me as well when preparing a recent challenge submission (perhaps [Dept.] is calculated itself so [DepartmentID] is a calculation of a calculation? If so, calculate both the field and the sort off the same base field).

Hope this helps, or please post a PBIX for the forum members to investigate further.

Greg

3

This is an interesting issue, I have always solved this type of problem in Power Query, instead of creating a calculated column with DAX, I do it with a custom column before the table is loaded to the model.

I had never even considered doing it as a calculated column.

Yet another example of multiple ways to do things in Power BI (just as in Excel)

2 Likes
4

Greg, thanks for the reply. Right after I posted this it occurred to me to do it in Power Query. Worked as I expected.

5

Thanks Heather. Exactly right. Right after I posted this it occurred to me to do it in Power Query. Worked as I expected.

1 Like
6

@lomorris @Greg Alternatively, you can do it in DAX but you have to make it a table using SUMMARIZE (not SUMMARIZECOLUMNS), with the switch statement, then you can sort it by that column. You would then have created a dimension table, and you can simply create a relationship back to the main table.

For example, this is using the data set for challenge #9:

Test = SUMMARIZE(filter(Trading,Trading[Haulage Currency]<>blank()),Trading[Haulage Currency],"ID",switch(Trading[Haulage Currency],
"(€) - Euro",1,
"(£) - Pound Sterling",2,
"($) - US Dollar",3,4))

image

And then of course utilize this table in your axis and slicers to have the right sort order.

1 Like