Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Need help with writing DAX

Hello Community,

Can you please help me writing this query in Power BI DAX (Column).

I have table, where I want to find the customer converstions  from first product  to their last product. i.e converstion from Product A to B and B to A.   Also, There is no customers in data that have purchased two products on same day. Just to avoid ambguity, I will exclude the customers that purchased on same day. 
Note -  I want to exclude the products that are purchased on First day of year. 

 

I would really appreciate if you could help me with it. 

Many Thanks, Chana

 

 

 

 

 

  • PaulDBrown's avatar
    PaulDBrown
    5 years ago

    Try this code for a calculated column:

     

     

     

    Product Conversion =
VAR _Table =
    CALCULATETABLE (
        'Table',
        FILTER (
            ALLEXCEPT ( 'Table', 'Table'[ID] ),
            YEAR ( 'Table'[Date] ) = YEAR ( EARLIER ( 'Table'[Date] ) )
        )
    )
VAR DistVal =
    CALCULATE (
        COUNT ( 'Table'[Product] ),
        FILTER (
            ALLEXCEPT ( 'Table', 'Table'[ID] ),
            YEAR ( 'Table'[Date] ) = YEAR ( EARLIER ( 'Table'[Date] ) )
        )
    )
RETURN
    IF (
        DistVal = 1,
        'Table'[Product] & "2" & 'Table'[Product],
        CONCATENATEX ( _Table, 'Table'[Product], "2", 'Table'[Date] )
    )

     

     

     

     

     

    I've added a new row for ID 1 in 2021 so you can see the progression in case you have more than 2 operations in a year

     

    As an alternative you could just list the progression based on the number of Products by year using:

     

    Product Conversion =
VAR _Table =
    CALCULATETABLE (
        'Table',
        FILTER (
            ALLEXCEPT ( 'Table', 'Table'[ID] ),
            YEAR ( 'Table'[Date] ) = YEAR ( EARLIER ( 'Table'[Date] ) )
        )
    )

RETURN
    CONCATENATEX ( _Table, 'Table'[Product], "2", 'Table'[Date] )

     

     

    To get:

     

9 Replies

  • PaulDBrown's avatar
    PaulDBrown
    Icon for Community Champion rankCommunity Champion
    5 years ago

    please post sample data (not an image) and a depiction of the expected outcome. Thanks!

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Hi PaulDBrown,  Please see below: I need result in column like Product Conversion TEST. 
      Note: I want excludes data on First day of  year. 
      Thank you in advance,

      Asad

      DateIDProductTEST COLProduct Conversion TEST
      1/1/2020 0:006AA OnlyA2A
      1/1/2020 0:007BB OnlyB2B
      2/12/2020 0:001AA OnlyA2A
      2/13/2020 0:006AA OnlyA2A
      3/12/2020 0:001AA OnlyA2A
      5/13/2020 0:005BB OnlyB2B
      12/5/2020 0:005BB OnlyB2B
      1/1/2021 0:001AA & BA2B
      1/1/2021 0:003AA OnlyA2A
      1/2/2021 0:002AA OnlyA2A
      1/3/2021 0:002AA OnlyA2A
      1/8/2021 0:001BA & BA2B
      4/14/2021 0:004BB OnlyB2B
      • PaulDBrown's avatar
        PaulDBrown
        Icon for Community Champion rankCommunity Champion
        5 years ago

        thanks for that. Do you want a measure or a calculated column?