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
lakshmi9228
Helper III
Helper III

Count of products

I have these products  I need to know, How many customers bought more than one product in a particular time period.

I need a filter , where i can select count of products such as 2, 3,4 etc 

At that time, I need to see the customers list. 

How can I get that, can someone help me out with this.     

 

For example, if I select count as 2, I need the customers list who had purchased any 2 products.

1 ACCEPTED SOLUTION

Hi @lakshmi9228,

 

To get the count of products accroding to the filered result, we need to create a measure like this.

 

DYcount = CALCULATE(COUNTROWS('Product'),FILTER(ALLSELECTED('Product'),'Product'[name]=MAX('Product'[name])))

Then we can create a slicer and create another measure. And filter the table visual based on the Measure, make the result is 1.

 

Measure = IF([DYcount]=SELECTEDVALUE(Table1[Filter]),1,0)

Here is the result for your reference.

1.PNG

 

For more details, please check the pbix as attached.

https://www.dropbox.com/s/hpo56lrvwl570sv/Distinct%20Customer%20Purchases.pbix?dl=0

 

Regards,

Frank

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

View solution in original post

13 REPLIES 13
v-frfei-msft
Community Support
Community Support

Hi @lakshmi9228,

 

Does that make sense? If so, kindly mark my answer as a solution to close the case.

 

Regards,

Frank

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.
Anonymous
Not applicable

Hi there, 

 

For this you need to use a Basket Analysis.. 

 

There is a nice writeup on this Here

 

 

It might help but it is not related to the solution I am looking for.
The below screenshot describes the way how I am looking for.Capture.PNG

Can someone please help me out

Hi @lakshmi9228,

 

Based on my test, we need to add a calculated column to your table to get the count of products.

 

Count = CALCULATE(DISTINCTCOUNT(Sales[Product]),FILTER(ALL(Sales),Sales[Customer]=EARLIER(Sales[Customer])))

 

Capture.PNG

For more details, please check the pbix as attached.

 

https://www.dropbox.com/s/wihx4i5bwuihkdd/Display%20Current%20and%20Previous%20Months.pbix?dl=0

 

Regards,

Frank

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

Hi Frank,

 

I tried using the below formulae, But I am not getting the exact customer list.
Please do let me know if there is any other way that I can go through it.

 

Hi @lakshmi9228,

 

I made another sample based on the data you shared here.

 

Firstly, we need create a calculated column using the formula as below.

 

Count = CALCULATE(DISTINCTCOUNT('Product'[Product]),FILTER(ALL('Product'),'Product'[name]=EARLIER('Product'[name])))

Then we can filter the table based on the calculated column.

 

1.PNG

 

For more details, please check the pbix as attached.

 

https://www.dropbox.com/s/hpo56lrvwl570sv/Distinct%20Customer%20Purchases.pbix?dl=0

 

Regards,

Frank

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

Hi Frank, 

Perfect, is there a way that I can apply filter on Orderdate and see the count of products according to the orders in a given time period.

Thanks,

Lakshmi

Hi @lakshmi9228,

 

To get the count of products accroding to the filered result, we need to create a measure like this.

 

DYcount = CALCULATE(COUNTROWS('Product'),FILTER(ALLSELECTED('Product'),'Product'[name]=MAX('Product'[name])))

Then we can create a slicer and create another measure. And filter the table visual based on the Measure, make the result is 1.

 

Measure = IF([DYcount]=SELECTEDVALUE(Table1[Filter]),1,0)

Here is the result for your reference.

1.PNG

 

For more details, please check the pbix as attached.

https://www.dropbox.com/s/hpo56lrvwl570sv/Distinct%20Customer%20Purchases.pbix?dl=0

 

Regards,

Frank

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

Hi Frank,

Sorry for the late reply.

I tried using the above measures but unable to figure how did you get the filter column in the new table.
Please do let me know.

 

Thanks,

Lakshmi

Hi @lakshmi9228,

 

Here I enter the filter table directly.

 

Capture.PNG

 

Regards,

Frank

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

Hi Frank,

Thank you so much for your patience - It is working perfectly.

lakshmi9228
Helper III
Helper III

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.