First and Last ID Column or Measure Help

[Anonymous]

Good day,

I tried diferent measures and columns, but the data I need will not present the value/text on the table correctly, 

FIRST ID = CALCULATE(VALUES(Table[ID]),FIRSTDATE(Table2[Date]))

ID	Date	User	Equipment	Results	First ID	Last ID
A000001	1/1/21 12:00 PM	John	Test 1	Pass	A000001	A000001
A000002	1/2/21 12:00 AM	John	Test 2	Fail	A000002	A000002
A000003	1/3/21 12:00 AM	John	Test 2	Pass	A000003	A000003
A000004	1/4/21 12:00 AM	John	Test 3	Pass	A000004	A000004
A000005	1/5/21 12:00 AM	Elvis	Test 1	Pass	A000005	A000005
A000006	1/6/21 12:00 AM	Elvis	Test 2	Pass	A000006	A000006
A000007	1/7/21 12:00 AM	Elvis	Test 2	Fail	A000007	A000007
A000008	1/8/21 12:00 AM	Mary	Test 1	Pass	A000008	A000008
A000009	1/9/21 12:00 AM	Mary	Test 2	Pass	A000009	A000009

First can I get help on the First and last ID, secondly With all of that data need to create Complete or not complete measure or column to determine if the testing is finished or not.

ID	Date	User	Equipment	Results	First ID	Last ID	If last ID is fail result should be not compelted, but also if las ID passed but is not last test result should be not completed
A000001	1/1/21 12:00 PM	John	Test 1	Pass	A000001	A000004	Completed
A000002	1/2/21 12:00 AM	John	Test 2	Fail	A000001	A000004	Completed
A000003	1/3/21 12:00 AM	John	Test 2	Pass	A000001	A000004	Completed
A000004	1/4/21 12:00 AM	John	Test 3	Pass	A000001	A000004	Completed
A000005	1/5/21 12:00 AM	Elvis	Test 1	Pass	A000005	A000007	Not completed
A000006	1/6/21 12:00 AM	Elvis	Test 2	Pass	A000005	A000007	Not completed
A000007	1/7/21 12:00 AM	Elvis	Test 2	Fail	A000005	A000007	Not completed
A000008	1/8/21 12:00 AM	Mary	Test 1	Pass	A000008	A000009	Not completed
A000009	1/9/21 12:00 AM	Mary	Test 2	Pass	A000008	A000009	Not completed

[v-chenwuz-msft · Accepted solution]

Hi @Anonymous,

First ID = CALCULATE(FIRSTNONBLANK('Table'[ID],0),ALLEXCEPT('Table','Table'[User]))

Last ID = CALCULATE(LASTNONBLANK('Table'[ID],0),ALLEXCEPT('Table','Table'[User]))

It is worth noting that,I understood you to say that the last test was Test 3, so I added to the condition whether it was Test 3.

If completed =

var _a = COUNTROWS(FILTER('Table',[Last ID]=EARLIER('Table'[Last ID]) && [Results]="Pass" && 'Table'[Equipment]="Test 3"))

return

IF(_a=BLANK(),"Not completed","Completed")

Best Regards

Community Support Team _ chenwu zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[parry2k]

@Anonymous here are the measures for first and last id:

First ID = CALCULATE ( MIN ( Data[ID] ), TOPN ( 1, FILTER ( ALL ( Data ), Data[User] = MAX ( Data[User] ) ),  Data[Date], ASC ) )

Last ID = CALCULATE ( MIN ( Data[ID] ), TOPN ( 1, FILTER ( ALL ( Data ), Data[User] = MAX ( Data[User] ) ),  Data[Date], DESC ) )

✨ Follow us on LinkedIn

Check my latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[parry2k]

@Anonymous are you looking for the first and last id for each user or full dataset? It is not very clear.