Forum Discussion
Grouping table by multiple columns
Hey there,
After two days of little progress, I'm asking for help.
I got a table of customers, and the products they purchsed. I added a calculated column based on different settings, identifying if the order is "Freeware" given by salesperson for free, or "Subscription" paid for by the customer.
This is an example table:
1. Each customer can have multiple products, and can be given multiple Freewares of the same product consecutively (e.g. first two rows). Customer can also renew paid subscriptions consecutively.
2. For each customer and product, I need to identify their last "free" order (if exists) and first "Sub" order of same product. To elaborate, if a customer was given 2 freewares of same product, I need to only display the last one based on "Start Date".
If a customer purchased a paid "sub" and then renewed it, I only care for the first one.
3. If a customer Did not receive any freewares, I don't want to display him at all.
4. The aim is to identify successful conversions from free to paid, and also display free subs not yet converted.
The end result needs to look like this (freeware and sub in same matching row):
I've marked the relevant rows from source table in bold to make it easier to understand.
Customer C is not in table as he did not have any freeware. Customer A with product KS is not in table because he never had freeware of this product.
I tried groupby, earlier, even calculated tables.
Any suggestions would be welcome.
Thank you!
Anonymous - OK, this involved creating a series of columns. Your dates mess me up a little but should work fine in your region settings. See attached PBIX below sig. You want Page 21 and Table (21).
8 Replies
Anonymous ,
First get a new column like this
Serial Sub =
var _1 = minx(filter(table, [customer] = earlier([customer]) && [serial] >earlier([customer])),[Serial])
Var _2 = minx(filter(table, [customer] = earlier([customer]) && [serial] =_1 ),[free/susribption])
return
if([free/susribption] = "Free" , _2 <>"Free" , _1, blank())with the help that get other columns
Start Date(Sub) =minx(filter(table, [customer] = earlier([customer]) && [serial] =earlier([Serial Sub]) ),[free/susribption])
or
Start Date(Sub) =minx(filter(table, [customer] = earlier([customer]) && [serial] =earlier([Serial Sub]) ),earlier([free/susribption]))same way get an end date and you can convert ="Y", when there is Serial Sub
Hey amitchandak Thanks for your quick reply!
1. "Serial Sub" is supposed to bring back the subsequent "sub" serial number of each freeware (if exists)?
2. if([free/susribption] = "Free" , _2 <>"Free" , _1, blank()) --this is a valid expression? It doesn't allow "2<>"Free" as expression inside if.
Please note the serial number is only for demo purposes, it is actually a random GUI text.
I can only match "freeware" to "sub" based on same customer and product, then identifing who is the latest freeware and first sub based on respective startdate.
Thank you!
Anonymous - Can you post that table as text in a table so that we can easily paste it into Power BI Enter Data query?
