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

alex1alex

Board Regular
Joined
Sep 12, 2014
Messages
57
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. <=???

jMuVRA9.png



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

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
Hi Alex,
Simplest way I can think of as a DAX calculated column is:
Code:
= CALCULATE ( MIN ( years[grade] ), ALLEXCEPT ( years, years[school] ) )
 
Upvote 0
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:"
 
Upvote 0
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] )
)
 
Upvote 0
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. )
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,557
Latest member
richa mishra

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top