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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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

 


@ 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!:
Mastering Power BI 2nd Edition

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?


@ 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!:
Mastering Power BI 2nd Edition

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

 


@ 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!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

That worked great! Thank you so much @Greg_Deckler !

@YOV - Happy to help! 🙂


@ 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!:
Mastering Power BI 2nd Edition

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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.