Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

First and Second Product

Hi, everyone, im trying to calculate a new column for the first and second product, I have 3 columns as User, Product and Date, what I want is the "FirstProduct" and "SecondProduct" as the example be...
Need Help
  • Ashish_Mathur's avatar
    Ashish_Mathur
    5 years ago

    Hi,

    Try these calculated column formulas

    First date of user = 

    =CALCULATE(MIN(Data[Date]),FILTER(Data,Data[User]=EARLIER(Data[User])))

    First product = 

    LOOKUPVALUE(Data[Product],Data[User],Data[User],Data[Date],Data[First date of user])

    Second date of user = 

    CALCULATE(MIN(Data[Date]),FILTER(Data,Data[User]=EARLIER(Data[User])&&Data[Date]>Data[First date of user]&&Data[Product]<>EARLIER(Data[First product])))

    Second product = 

    =LOOKUPVALUE(Data[Product],Data[User],Data[User],Data[Date],Data[Second date of user])

Mikelytics's avatar
Mikelytics
Icon for Resident Rockstar rankResident Rockstar
5 years ago

Hi Anonymous 

 

Please try the following:

 

first product = 
SELECTCOLUMNS(
    TOPN(
        1,
        FILTER(ALL(fact_table_d),
            fact_table_d[Customer]=EARLIER(fact_table_d[Customer])
        )
        ,
        fact_table_d[Date],
    ASC 
    )
,"any name, it does not matter"
,fact_table_d[Product]
)

This calulcated column identifies all rows of the customer and sorts it by date. Then it takes value of the product column from the first row which is the first product bought by the customer.

 

for the seconf product use the following

second product = 
SELECTCOLUMNS(
    TOPN(
        1,
        FILTER(ALL(fact_table_d),
            fact_table_d[Customer]=EARLIER(fact_table_d[Customer])
            && fact_table_d[Product] <> EARLIER(fact_table_d[first product])
        )
        ,
        fact_table_d[Date],
    ASC 
    )
,"any name"
,fact_table_d[Product]
)

It is nearly the same query with the only exception that it also filters out the first product bought. So taht if somebody bought the same product twice in a row it will be ignored.

 

Best regards

Mikelytics

 

Did I solve your request? Please mark my post as solution.

 

Appreciate your Kudos.