Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
YOV
Helper II
Helper II

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.PNG

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):

2.PNG

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!

1 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).

Greg_Deckler_0-1599398693518.png

 


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

8 REPLIES 8
amitchandak
Super User
Super User

@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

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!

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


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

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

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

Greg_Deckler_0-1599398693518.png

 


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

That worked great! Thank you so much @Greg_Deckler !

@YOV - Happy to help! 🙂


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

CustomerProductSerial (random GUID)StartDateEndDatePeriod (M)Free / Subscription
AWA156851/1/201931/12/201912Free
AWA677321/1/202031/5/20205Free
AWA121141/6/202030/6/20201Sub
AWA607191/7/202030/6/202112Sub
AQWS327891/1/201931/12/201912Free
AKS480651/7/202030/6/202112Sub
BWA189441/1/202031/5/20205Free
BWA563111/6/202030/6/20201Sub
BKS777241/7/202030/6/202112Sub
CQWS196151/7/202030/6/202112Sub
CWA708671/7/202030/6/202112Sub
DWA306561/6/202030/6/20201Sub

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors