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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Active ProductPath for each customer

Hey all,

 

I need some help with an "ActiveProductPath", hopefully someone can help me. 

 

I want for each customer the path of the active products, see attachment,

  https://www.dropbox.com/s/1jfqupyrfdwks3t/Dummy-file%20ActiveProductPath.xlsx?dl=0 

 

For example customer 1 have bought 4 products in 2019. 

On 01-04-2019 was only product A active, on 01-07-2019 also product A, on 01-10-2019 the products B and C were active and on 01-01-2020 is product D active.

 

The endresult on 01-04-2019 should be:

CustomerActiveProductPath
1A
2E
3H,I
4 

 

Can someone help me?

 

Thanks in advance!

 

Greetings from The Netherlands,


Cor

1 ACCEPTED SOLUTION
dax
Community Support
Community Support

Hi corvada,

I change something in the data model based on your requirement , you could refer to my sample to see whether it work or not.

Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

View solution in original post

9 REPLIES 9
dax
Community Support
Community Support

Hi corvada, 

I think the Month in column should be continuous, you could refer to my sample for details.

Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

Anonymous
Not applicable

Excellent solution Zoe Zhi, thank you very much!!

Last part of the challenge: 

For every customer I want for each ref Ref_Date’[FullDate] the ActiveProductPath in a table in Power Query so I can use a slicer and count the customers who had a certain ActiveProductPath.
In the end I want an answer for the question: “how many customers have ActiveProductPath “x” on a certain day?”
Can you help me with the last part of the solution?

 

Thanks in advance! 

 

 

Anonymous
Not applicable

I've made another dummyfile, https://www.dropbox.com/s/cjkjr4b5184v2y6/Dummy-file%20ActiveProductPathv2.xlsx?dl=0

and https://www.dropbox.com/s/1828euve4n17gry/ActiveProductPathv2.pbix?dl=0

 

Hopefully you will understand what I mean.

 

I want to count each ActiveProductsPath for each date.

The result should be:

Date A B C A, B
1-4-201921  
1-7-201911 1
1-10-2019   2
1-1-2020 12 

Can someone help me?

 

dax
Community Support
Community Support

Hi corvada,

According to your description, it seems that you want to show the "A, B" in column , right? As I know, you need to use field instead of measure in column of Matrix. You might need to change the data structure. I am still working on this and will inform you as soon as I get it.

Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Anonymous
Not applicable

Hi Zoe Zhi,

 

You are right, I need a calculated column in a separate column in the data which calculates the active productpath for each customer. I don't know how to do that, hopefully you know 🙂

 

Best regards from The Netherlands,


Cor

 

dax
Community Support
Community Support

Hi corvada,

I change something in the data model based on your requirement , you could refer to my sample to see whether it work or not.

Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

Anonymous
Not applicable

Impressive work Zoe Zhi, I'm very pleased with your solution, thank you!

Last question, how can I change the following M-code to get unique ActiveProductPaths?

= Table.Group(#"Type gewijzigd", {"Aangepast", "Customer"}, {{"Path", each Text.Combine(List.Sort(Text.Split(Text.Combine([Product], ","), ","),Order.Ascending),", "), type text}})

 

On the 29th of July we have the productpaths "B" and "B, B". I want them to get only B, how can I change the formula?

 

https://www.dropbox.com/s/zvrxfymkw5x9tp7/Photo%20ActiveProductPath.png?dl=0

https://www.dropbox.com/s/opsqiam7fwmvzbw/ActiveProductPathv3.pbix?dl=0

 

Thanks in advance! 

 

Regards,

 

Cor

 

Anonymous
Not applicable

I already know the answer:  Table.Group(#"Type gewijzigd", {"Aangepast", "Customer"}, {{"Path", each Text.Combine(List.Sort(Text.Split(Text.Combine(List.Distinct([Product]), ","), ","),Order.Ascending),", "), type text}})

 

Thanks Zoe Zhi!!!!!! 🙂 

Anonymous
Not applicable

Question, the dummy files contains 15 rows, the table with the solution contains more than 900 rows... What if I would use the solution for 7 million products? Is it possible to make a solution with virtual tables instead of physical tables? If so, how? 😉

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.