Find the nth largest value from multiple columns ("fields") for each row ("observation")

[JefJacobs]

Hi all. Newbie to DAX here. This issue is puzzling me. I know how to do it in Excel, but there must be a way in DAX/PBI. 

Let's assume I have a table where each row represents a unique student in a school. So, each row represents a unique student, e.g., Student1, Student2, ... StudentN. Assume there different subjects that each learners participates in. Subjects are the columns, so e.g,. History, Math, Biology, etc. For every subject a student has a test score out of 100 (the data).

I can find the Max score for each learner. But what I need is a new column where the second lowest score out of each of the subjects is presented. Bonus points if a second new column is generated that returns the column name (i.e., Subject name) related to the second lowest score is presented. I'm not concerned if there are two or more subjects with the same second lowest score. 

Name	Biology	Math	History	Accounting	DAX	2ndLowScore	2ndLowSubject
Student1	55	65	75	25	25	55	Bilogy
Student2	25	26	27	37	47	26	Math
Student3	45	67	23	67	12	23	History
Student4	76	45	23	45	85	45	Math

[eliasayyy · Accepted solution]

hello @JefJacobs ,

lets say this is your data

it looks like your table is pivoted so lets reverse it 

select all the subjects like so

press on transform in the ribbon and choose unpivot columns

your results will be this

now load your data and lets go back to dax

it looks like you want to find the second lowest grade for each student 

start by creating a new calculated column called rank grades

Rank Grades = 
RANKX(FILTER('Table','Table'[Name]=EARLIER('Table'[Name])),'Table'[Value],,DESC,Dense)

this measure will rank your grades by student id

result

now create other columns 

Second low Grade = 
CALCULATE(
    MAX('Table'[Value]),ALLEXCEPT('Table','Table'[Name]),'Table'[Rank Grades] = 2)

and 

Second Grade Subject = 
VAR _c = CALCULATE(
    COUNT('Table'[Value]),ALLEXCEPT('Table','Table'[Name]),'Table'[Rank Grades] = 2)
    return
IF(_c =1 , CALCULATE(MAX('Table'[Subject]),ALLEXCEPT('Table','Table'[Name]),'Table'[Rank Grades] = 2),
CONCATENATEX(FILTER('Table','Table'[Name]=EARLIER('Table'[Name]) && 'Table'[Rank Grades] = 2),'Table'[Subject]," || ")
)

final result

if you want to repivot it just do a new table 

Pivot = 
SUMMARIZE(
    'Table',
    'Table'[Name],
    "Accounting" , CALCULATE(SUM('Table'[Value]),'Table'[Subject] = "Accounting"),
    "Math" , CALCULATE(SUM('Table'[Value]),'Table'[Subject] = "Math"),
    "Biology" , CALCULATE(SUM('Table'[Value]),'Table'[Subject] = "Biology"),
    "History" , CALCULATE(SUM('Table'[Value]),'Table'[Subject] = "History"),
    "Second Lowest Grade" , MAX('Table'[Second low Grade]),
    "Subject Lowest" , MAX('Table'[Second Grade Subject])
)

[JefJacobs]

Thank you very much! In my actual file I have 100k+ students and 50 subjects. So, a bit of writing to do. But this will work. Will the pivoting and repivoting affect my other calculated columns? Or should I just redo my other measures after repivoting? 

[eliasayyy]

if you inpivot then yes it might affect your other calculations becasue you are making all subjects into one column