Forum Discussion

king2005r's avatar
king2005r
Icon for Helper III rankHelper III
4 years ago
Solved

Check the previous column value, with default status

Hi Everyone, 

 

I need your help, I have very sample request

 

I want the formula to check if there any previous "active" orders for the same customer ID that has "Active" status so the final output will be "Not yet" status regardless of any other status

 

Data does not include any date. 

 

Example :

 

Customer IDOrder NumberCustomer nameStatus
114456JohanActive
114876JohanClosed
211222Test1Active
211484Test1Close
211454Test1Close
317461SarahClose

 

Output : 

Customer IDOrder NumberCustomer nameStatusAll customer package deliver
114456JohanActiveNot yet
114876JohanClosedNot yet
211222Test1ActiveNot yet
211484Test1CloseNot yet
211454Test1CloseNot yet
317461SarahCloseYes

 

 

  • Jihwan_Kim's avatar
    Jihwan_Kim
    4 years ago

     

    All customers package deliver CC =
    IF (
    "Active"
    IN SUMMARIZE (
    FILTER ( Data, Data[Customer ID] = EARLIER ( Data[Customer ID] ) ),
    Data[Status]
    ),
    "Not yet",
    "Yes"
    )

5 Replies

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Icon for Super User rankSuper User
    4 years ago

    Hi,

    Please check the below picture and the attached pbix file.

    It is for creating a calculated measure.

     

     

    All customers package deliver: =
    IF (
    HASONEVALUE ( Data[Customer ID] ),
    IF (
    "Active"
    IN CALCULATETABLE (
    VALUES ( Data[Status] ),
    ALLEXCEPT ( Data, Data[Customer ID] )
    ),
    "Not yet",
    "Yes"
    )
    )

    • king2005r's avatar
      king2005r
      Icon for Helper III rankHelper III
      4 years ago

      Hi 

      Thank you for your reply, the attached solution is measurement, can I have it as a column? 

      • Jihwan_Kim's avatar
        Jihwan_Kim
        Icon for Super User rankSuper User
        4 years ago

         

        All customers package deliver CC =
        IF (
        "Active"
        IN SUMMARIZE (
        FILTER ( Data, Data[Customer ID] = EARLIER ( Data[Customer ID] ) ),
        Data[Status]
        ),
        "Not yet",
        "Yes"
        )

  • tamerj1's avatar
    tamerj1
    Icon for Community Champion rankCommunity Champion
    4 years ago

    Hi king2005r 

    You can use the following for a calculated column 

    All Customer Package Delivered =
IF (
    COUNTROWS (
        FILTER (
            Data,
            Data[Customer ID] = EARLIER ( Data[Customer ID] )
                && Data[Status] = "Active"
        )
    ) > 0,
    "Not yet",
    "Yes"
)