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

[alex1alex]

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]

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

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

 

[alex1alex]

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]

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]

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]

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