Forum Discussion

adam_mac's avatar
adam_mac
Helper I
5 years ago
Solved

Power Query: Checking if a trial customer has fully purchased trial product

Hi, i have a dataset that captures if a customer has purchased a trial of a product, if they purchased at a discount, or if they fully purchased.   What i would like to do is to create an additiona...
Need Help
  • adam_mac's avatar
    adam_mac
    5 years ago

    Solved the issue from a reddit user by creating a calculated table:

     

    tbl2 = var tbl = addcolumns( SUMMARIZE( filter('Table','Table'[PurchaseMethod]="Full Purchase"), 'Table'[Customer ID]), "blank",'Table'[Customer ID]) return addcolumns( SUMMARIZE( filter('Table', 'Table'[Product Name]='Table'[Product Name] &&'Table'[PurchaseMethod]="Full Purchase" &&'Table'[Customer ID] in summarize(filter(tbl,[Customer ID]),'Table'[Customer ID]) ), 'Table'[Product Name],'Table'[Customer ID],'Table'[PurchaseMethod]), "test",1)

     

    and adding a column to orginal table:

    FullPurchaseMade = if('Table'[PurchaseMethod]="Trial",lookupvalue(tbl2[test],tbl2[Product Name],'Table'[Product Name],tbl2[Customer ID],'Table'[Customer ID]),blank())

     

adam_mac's avatar
adam_mac
Helper I
5 years ago

Solved the issue from a reddit user by creating a calculated table:

 

tbl2 = var tbl = addcolumns( SUMMARIZE( filter('Table','Table'[PurchaseMethod]="Full Purchase"), 'Table'[Customer ID]), "blank",'Table'[Customer ID]) return addcolumns( SUMMARIZE( filter('Table', 'Table'[Product Name]='Table'[Product Name] &&'Table'[PurchaseMethod]="Full Purchase" &&'Table'[Customer ID] in summarize(filter(tbl,[Customer ID]),'Table'[Customer ID]) ), 'Table'[Product Name],'Table'[Customer ID],'Table'[PurchaseMethod]), "test",1)

 

and adding a column to orginal table:

FullPurchaseMade = if('Table'[PurchaseMethod]="Trial",lookupvalue(tbl2[test],tbl2[Product Name],'Table'[Product Name],tbl2[Customer ID],'Table'[Customer ID]),blank())