Returning a field value based on information in two columns

[swinchell]

I need help writing a DAX function that returns the first test result for an individual user. For example, William has taken test 1 twice, but I only need to know that he passed it on attempt one. In this case, if there are multiple test results for a user, I am only interested in the first result. Thank you.

Sample table.

User	Test#	Result
Joe	Test1	Pass
Carol	Test1	Pass
William	Test1	Pass
William	Test1	In Progress
Steven	Test1	Not Started
Joe	Test2	Not Started
Steven	Test1	Pass
Carol	Test2	Not Started
William	Test2	Pass
William	Test2	In Progress

[Anonymous]

Hi @swinchell ,

Please refer this formula.

Measure = 
var _min_date = CALCULATE(MIN('Table'[date]),FILTER(ALLSELECTED('Table'),'Table'[User]=SELECTEDVALUE('Table'[User])&&'Table'[Test#]=SELECTEDVALUE('Table'[Test#])))
return
CALCULATE(MAX('Table'[Result]),FILTER(ALLSELECTED('Table'),'Table'[User]=SELECTEDVALUE('Table'[User])&&'Table'[date]=_min_date))

Best Regards,

Jay

[amitchandak · Accepted solution]

@swinchell , do you have date column to check the first date.

Or add an index column in power query https://stackoverflow.com/questions/45715963/creating-an-index-column-for-power-bi

Column =

var _1 = minx(filter(table, [user] = earlier([user]) && [Test #] = earlier([Test #]) ), [index] )
return
if(_1 =[Index] && [Result] = "Pass" , 1,0)