Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

First and Last ID Column or Measure Help

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]))
 
IDDateUserEquipmentResultsFirst IDLast ID
A0000011/1/21 12:00 PMJohnTest 1PassA000001A000001
A0000021/2/21 12:00 AMJohnTest 2FailA000002A000002
A0000031/3/21 12:00 AMJohnTest 2PassA000003A000003
A0000041/4/21 12:00 AMJohnTest 3PassA000004A000004
A0000051/5/21 12:00 AMElvisTest 1PassA000005A000005
A0000061/6/21 12:00 AMElvisTest 2PassA000006A000006
A0000071/7/21 12:00 AMElvisTest 2FailA000007A000007
A0000081/8/21 12:00 AMMaryTest 1PassA000008A000008
A0000091/9/21 12:00 AMMaryTest 2PassA000009A000009

 

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.

 

IDDateUserEquipmentResultsFirst IDLast IDIf 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
A0000011/1/21 12:00 PMJohnTest 1PassA000001A000004Completed
A0000021/2/21 12:00 AMJohnTest 2FailA000001A000004Completed
A0000031/3/21 12:00 AMJohnTest 2PassA000001A000004Completed
A0000041/4/21 12:00 AMJohnTest 3PassA000001A000004Completed
A0000051/5/21 12:00 AMElvisTest 1PassA000005A000007Not completed
A0000061/6/21 12:00 AMElvisTest 2PassA000005A000007Not completed
A0000071/7/21 12:00 AMElvisTest 2FailA000005A000007Not completed
A0000081/8/21 12:00 AMMaryTest 1PassA000008A000009Not completed
A0000091/9/21 12:00 AMMaryTest 2PassA000008A000009Not completed
Need Help
Tips and Tricks
  • v-chenwuz-msft's avatar
    v-chenwuz-msft
    4 years ago

    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.

3 Replies

  • parry2k's avatar
    parry2k
    Super User
    4 years ago

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

  • parry2k's avatar
    parry2k
    Super User
    4 years ago

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

  • v-chenwuz-msft's avatar
    v-chenwuz-msft
    Community Support
    4 years ago

    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.