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

alex1alex

Board Regular
Joined
Sep 12, 2014
Messages
56
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'.
 

Some videos you may like

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

Ozeroth

Active Member
Joined
Dec 14, 2013
Messages
264
Hi Alex,
Simplest way I can think of as a DAX calculated column is:
Code:
= CALCULATE ( MIN ( years[grade] ), ALLEXCEPT ( years, years[school] ) )
 

alex1alex

Board Regular
Joined
Sep 12, 2014
Messages
56
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

Active Member
Joined
Dec 14, 2013
Messages
264
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:
Code:
= Table.AddColumn(
[B]      PreviousStep[/B],
      "sortby",
      each let CurrentSchool = [school] in List.Min( Table.SelectRows([B]PreviousStep[/B], each [school] = CurrentSchool )[grade] )
)
 

alex1alex

Board Regular
Joined
Sep 12, 2014
Messages
56
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. )
 

Watch MrExcel Video

Forum statistics

Threads
1,099,092
Messages
5,466,589
Members
406,491
Latest member
amirkhosravi

This Week's Hot Topics

Top