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

Customer Frequency Bins - DAX

Hi all, 

 

I am trying to visualize frequency bins of customers visits (eg - number of customers who visited between 2-5 times, 6-10 times, 10+ times etc) in a given period (based on date slicer). I have a Table1 with customer_Id, date(eg - 11/12/2018) and order_id. So customer_id is repeated in rows based on number of times a customer visited.  Now I  used calcualted table to get the results.

 

Cust-Freq-Table = summarize('Table1','Table1'[Customer_Id],
"Customer-Freq",COUNT('Table1'[Customer_Id]))

 

But the problem is it is not filtering by date slicer. What can I do ? Please suggest. 

Thanks!

3 ACCEPTED SOLUTIONS

@Anonymous solution attached, you can tweak it as per your needs.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

View solution in original post

@Anonymous and also make sure the sort order on visual is by bin, you can check that by clicking three dots on top right corner of the visual.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

View solution in original post

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

16 REPLIES 16
parry2k
Super User
Super User

@Anonymous hey if you share some sample data in excel using onedrive/google drive and can get you the solution. 



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Anonymous
Not applicable

@parry2k Works perfectly!  Thanks for quick reply. I missed the trick of sorting in the visual by 3 dots. 

Anonymous
Not applicable

@parry2k   Here is the link to the excel file https://drive.google.com/open?id=1TvR_S4du3o7eYxGxfe9_qM55uQjr73K2  

I have only "raw" sheet  with me. I need to get to the graph in "bin" sheet when the date slicer is 11/12/2018 to 12/11/2018 (when I change the dates I expect graph to change but that is not happening prperly with my current dax).  Also I want bin sizes in order in the x-axis like [1-2] then [3-5] and then [6-10]. 

I hope that helps. 

Thanks!

@Anonymous link is not reachable, can you reshare it?



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Anonymous
Not applicable

@parry2k  My apologies...

https://drive.google.com/drive/folders/146Da7nfCDA2hIhFiYDOmZT3OaLKnEezA?usp=sharing

Also, do not want to count all the rows with blank CustomerID.  

Thanks!

 

 

Hi,

Based on the dataset that you have shared, what result are you expecting?


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

@Ashish_Mathur  I have shared the excel which has 3 sheets. Raw sheet is that  I have. I am expecting a graph in third (bin) sheet. It should change accordingly when I change the date slicer. Also x-axis should be in order [1-2] then [3-5] etc....  

@parry2k  gave an accurate solution. but my visual is not filtering the other visuals..What I am missing?

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

@Anonymous solution attached, you can tweak it as per your needs.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Thanks a lot for the solution, was trying to get same result for almost the whole last week. Googleing myself to death and almost giving up until I found this post 🙂 This was awsome. 

You are welcome.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

@parry2k  This works perfectly. One minor problem though. I am not getting x-axis by the bin sizes. I am using  the exact code you sent me. Seems it is always rearranging based on y axis values. 

Capture.PNG

 

Thanks!

@Anonymous i have id column in bin table, you need to sort bin column by id column so that it shows labels correctly.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

@Anonymous and also make sure the sort order on visual is by bin, you can check that by clicking three dots on top right corner of the visual.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Anonymous
Not applicable

@parry2k  One more question. This works fine but the visual I got fro this cannot be used to crossfilter visuals made from raw table/other tables right ? How can I aceive that ? Thanks!

Anonymous
Not applicable

Thanks!

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.