Forum Discussion
Helper IPower 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 additional column that captures: If a customer purchased a trial product, did they go onto fully purchase or discount purchase that same product. For e.g. if a customer purchased a trial of apples, did they later buy a full version of apples (ive changed the products for posting here).
I have no idea how i would go about doing this without creating a bunch of seperate tables, filtering, lookups etc so any help much appreciated.
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())
3 Replies
- adam_mac
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())
- Greg_Deckler
Community Champion
adam_mac You should be able to use the technique here: See my article on Mean Time Between Failure (MTBF) which uses EARLIER: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/339586.
The basic pattern is:
Column =
VAR __Current = [Value]
VAR __PreviousDate = MAXX(FILTER('Table','Table'[Date] < EARLIER('Table'[Date])),[Date])
VAR __Previous = MAXX(FILTER('Table',[Date]=__PreviousDate),[Value])
RETURN
__Current - __Previous - adam_mac
Greg_Deckler thanks for input. Im not really sure how to apply this to my scenario. Are you able to provide further info?
