Grouping table by multiple columns

[YOV]

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!

[amitchandak]

@YOV ,

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

[YOV]

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!

[Greg_Deckler]

@YOV - Can you post that table as text in a table so that we can easily paste it into Power BI Enter Data query?

[YOV]

Hey @Greg_Deckler , here is the table in plain text.

Tried adding it in table but it marks the post as spam:

Customer Product Serial (random GUID) StartDate EndDate Period (M) Free / Subscription
A WA 15685 1/1/2019 31/12/2019 12 Free
A WA 67732 1/1/2020 31/5/2020 5 Free
A WA 12114 1/6/2020 30/6/2020 1 Sub
A WA 60719 1/7/2020 30/6/2021 12 Sub
A QWS 32789 1/1/2019 31/12/2019 12 Free
A KS 48065 1/7/2020 30/6/2021 12 Sub
B WA 18944 1/1/2020 31/5/2020 5 Free
B WA 56311 1/6/2020 30/6/2020 1 Sub
B KS 77724 1/7/2020 30/6/2021 12 Sub
C QWS 19615 1/7/2020 30/6/2021 12 Sub
C WA 70867 1/7/2020 30/6/2021 12 Sub
D WA 30656 1/6/2020 30/6/2020 1 Sub

[Greg_Deckler · Accepted solution]

@YOV - 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).

[YOV]

That worked great! Thank you so much @Greg_Deckler !

[Greg_Deckler]

@YOV - Happy to help! 🙂

[YOV]

Customer	Product	Serial (random GUID)	StartDate	EndDate	Period (M)	Free / Subscription
A	WA	15685	1/1/2019	31/12/2019	12	Free
A	WA	67732	1/1/2020	31/5/2020	5	Free
A	WA	12114	1/6/2020	30/6/2020	1	Sub
A	WA	60719	1/7/2020	30/6/2021	12	Sub
A	QWS	32789	1/1/2019	31/12/2019	12	Free
A	KS	48065	1/7/2020	30/6/2021	12	Sub
B	WA	18944	1/1/2020	31/5/2020	5	Free
B	WA	56311	1/6/2020	30/6/2020	1	Sub
B	KS	77724	1/7/2020	30/6/2021	12	Sub
C	QWS	19615	1/7/2020	30/6/2021	12	Sub
C	WA	70867	1/7/2020	30/6/2021	12	Sub
D	WA	30656	1/6/2020	30/6/2020	1	Sub