Forum Discussion

SofiaTeles's avatar
SofiaTeles
Frequent Visitor
3 years ago

Calculated column displaying the first and last value/text shown in another column.

Hi, 

 

Please consider this sample purchases dataset: 

 

ClientPurchase DateOrder codePromocode
A14/04/2023A001code40
B15/04/2023A002code50
C16/04/2023A003 
D17/04/2023A004 
A18/04/2023A005code40
B19/04/2023A006code30
C20/04/2023A007code30
D21/04/2023A008 
A22/04/2023A009 
C23/04/2023A010

 

 

I'm trying to analyze the performance of the promo codes. I want to determine if customers continue to make purchases from us after using a promocode. To do this, I plan to create two calculated columns: "first_promocode" and "last_promocode" (as a customer may use multiple promo codes, I haven't decided yet which one to consider). I want these columns to be associated with the customer. The expected output is as follows:

 

ClientPurchase DateOrder codePromocodeFirst PromocodeLast promocode
A14/04/2023A001code40code40code40
B15/04/2023A002code50code50code30
C16/04/2023A003 code30code30
D17/04/2023A004   
A18/04/2023A005code40code40code40
B19/04/2023A006code30code50code30
C20/04/2023A007code30code30code30
D21/04/2023A008   
A22/04/2023A009 code40code40
C23/04/2023A010 code50

code30

 

Can you please help me find a DAX function for this? I haven't been very successful on my own.

 

Thank you in advance.

Best regards, 

Sofia Teles

Need Help

5 Replies

  • DOLEARY85's avatar
    DOLEARY85
    Icon for Resident Rockstar rankResident Rockstar
    3 years ago

    Hi, 

     

    try these calculated columns:

     

    First Promo Used = CALCULATE(LOOKUPVALUE('Table (6)'[Promocode],'Table (6)'[Purchase Date],CALCULATE(MIN('Table (6)'[Purchase Date]))),FILTER(ALLEXCEPT('Table (6)','Table (6)'[Client]),'Table (6)'[Promocode]<>BLANK()))
     
    Last Promo Used = CALCULATE(LOOKUPVALUE('Table (6)'[Promocode],'Table (6)'[Purchase Date],CALCULATE(MAX('Table (6)'[Purchase Date]))),FILTER(ALLEXCEPT('Table (6)','Table (6)'[Client]),'Table (6)'[Promocode]<>BLANK()))
     

    If I answered your question, please mark my post as solution, Appreciate your Kudos 👍

  • SofiaTeles's avatar
    SofiaTeles
    Frequent Visitor
    3 years ago

    Hi DOLEARY85 

     

    Thank you so much for your quick reply.

     

    Power Bi is returning this error: "A multi-value table was provided where a single value was expected."

     

    Do you know how can I solve it? I might have simplified the dataset presented here compared to the one I'm using.

     

    Thanks in advance...

     

    Best regards, 

    Sofia

    • DOLEARY85's avatar
      DOLEARY85
      Icon for Resident Rockstar rankResident Rockstar
      3 years ago

      Hi,

       

      i don't suppose you can share the PBIX file?