# How to return the rows that equal the current row (for a calculated column)



## alex1alex (May 23, 2016)

Hello, 
My end goal is to create a sortby column as shown  here. (so that I can sort [school] by ... [sortby])
I need the formula for the "sortby" calculated column.
For named "Years"
It should be something like:  =MIN(Years[grade])  
                                                where years[school] = years[school] OF THIS ROW.    <=???








Thanks!
Alex

*in powerbi i get this error if I use [grade] for the sort by column
Sort By Another Column Error 

We cannot sort the 'school' column by 'grade'. You can't have more than one value in 'grade' for the same value in 'school'. Choose a different column for sorting or update the data in 'Year'.


----------



## Ozeroth (May 23, 2016)

Hi Alex,
Simplest way I can think of as a DAX calculated column is:

```
= CALCULATE ( MIN ( years[grade] ), ALLEXCEPT ( years, years[school] ) )
```


----------



## alex1alex (May 24, 2016)

Thanks Owen,
That gives me the column i was looking for.......
unfortunately,   when i try to use it as the "sort by column", power bi complains about a circular dependency.  "A circular dependency was detected:"


----------



## Ozeroth (May 24, 2016)

I see...
I get the same error in Power Pivot for Excel.

In Power BI Desktop I get a different message, but I guess it's the same problem:
*Sort By Another Column Error
This column can't be sorted by a column that is already sorted, directly or indirectly, by this column.

*So it appears that you can't sort by a calculated column whose DAX expression depends on the column you want to sort.

I think you'll just have to handle this at the ETL stage, say in Power Query. 
There are several ways you could replicate the behaviour of the above DAX expression in Power Query (making no assumptions about sort order of rows) such as:

```
= Table.AddColumn(
[B]      PreviousStep[/B],
      "sortby",
      each let CurrentSchool = [school] in List.Min( Table.SelectRows([B]PreviousStep[/B], each [school] = CurrentSchool )[grade] )
)
```


----------



## alex1alex (May 24, 2016)

Cheers for the PowerQuery line, that's great.  I'll add that to my example  notebook.
In fact, I did as you mentioned and added some sorting info in my date(aka, grade) table.

Thanks Owen!

PS, Say hi to StephenG if you run across him ( he's a common contact between us in LinkedIn. )


----------



## Ozeroth (May 27, 2016)

No worries 
Well whaddaya know, small world, I will say hi


----------

