Forum Discussion

jgray72's avatar
jgray72
Helper II
7 years ago
Solved

How Can I Count Breaks in a Pattern?

Hi - in the below data set, I want to count the number of styles that have a break in their buying pattern.  For example, I want to know that two out of the 4 styles don't have consecutive buys.  Sty...
Need Help
  • Ashish_Mathur's avatar
    Ashish_Mathur
    7 years ago

    Hi,

     

    You may download my MS Excel file from here.  The same file can be imported into PBI desktop as well.

  • Ashish_Mathur's avatar
    Ashish_Mathur
    7 years ago

    Hi,

     

    You may download my revised PBI file from here.

     

    Hope this helps.

     

jgray72's avatar
jgray72
Helper II
7 years ago

Hi - I played with this formula some more and it doesn't work.  It doesn't tell the difference between a buying pattern without breaks vs. one that has breaks.  I coverted it to DAX and it just gives the number of buys that aren't one time buys.  Still working it, as the solution is proving to be evasive!

 

183012 should not show up below as having intermittent purchases.

 

Ashish_Mathur's avatar
Ashish_Mathur
Super User
7 years ago

Hi,

 

You may download my revised PBI file from here.

 

Hope this helps.

 

  • jgray72's avatar
    jgray72
    Helper II
    7 years ago

    Thank you so much!  It works!!!  

     

    Appreciate your help.

     

    Julie

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    7 years ago

    You are welcome.

  • jgray72's avatar
    jgray72
    Helper II
    7 years ago

    Hi Ashir - I ran into a problem after I continued to spot check the data.  The thing I didn't account for in the sample data, is that there can be several rows of data, as multiple regions can buy.

     

    I think that's why the counts aren't correct below.  I don't think it's accurately counting the purchase instances at an aggregated level.  In this example, I should get one consecutive bought style and one non-consecutive bought style.  I should have made my sample data better reflect my raw data.

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    7 years ago

    Hi,

     

    So there is nothing wrong with my solution.  If your requirements keep changing, it is quite obvious that the solution would require tweaking.  Please do not be in a hurry to just post some sample which does not reflect yoru actual dataset.  Share a well thought over dataset, describe the business question in detail and then show the expected result.

  • jgray72's avatar
    jgray72
    Helper II
    7 years ago

    Thanks Ashir - new to DAX and Power BI, so learning a lot on the fly.  I will continue to tweak.  I've spent over 2 weeks trying to figure this out and I just can't nail it down.  I'm a business user vs. an IT person, so I'm not used to "writing" reports.  I don't know SQL, VBA, etc.  

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    7 years ago

    Hi,

     

    I am an end business user myself and have absolutely no knowledge about any programming language.  Whenever you post any question, please ensure that the dummy dataset resembles your actual dataset and you describe the business question very clearly so that the person helping you can come up with a robust solution.