topic Re: Returning a field value based on information in two columns in DAX Commands and Tips

Returning a field value based on information in two columns

Anonymous — Wed, 02 Mar 2022 02:23:58 GMT

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

Re: Returning a field value based on information in two columns

amitchandak — Wed, 02 Mar 2022 02:47:08 GMT

Anonymous , 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)

Re: Returning a field value based on information in two columns

Anonymous — Fri, 04 Mar 2022 02:43:05 GMT

Hi Anonymous ,

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