Forum Discussion

zedleb's avatar
zedleb
Frequent Visitor
8 years ago
Solved

Count changes in a sequence; as.Timeline visualization

Hi all

 

I'm using as.Timeline very successfully to illustrate the number of times a product has changed on a line of production.  As each change represents downtime – it is an important datum to capture.

 

 

It is easy enough to count by eye on a slow line – see the example below with 5 changes -  but when we are looking at some busier lines or a longer timescale it becomes impossible to count.

 

 

 

I have been trying to capture this count of changes but I am struggling to identify every change in the sequence because often there may be a change back to a previous reference for which my calculation give the same id.  This is the case for change number 4 above which my sequence calculation reiterates as sequence ‘7’ (see picture below).  So a distinctcount of sequence changes here incorrectly gives 4 changes instead of the 5 that as.Timeline correctly identifies.

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Can anyone help me with a DAX/M solution that will give me the numerical output that this great visual so beautifully illustrates? 

 

Thanks for reading,

zedleb

 

Need Help

3 Replies

    • zedleb's avatar
      zedleb
      Frequent Visitor
      8 years ago

      Hi Sam

       

      Thanks for your rapid response!  I've taken a look at both your solution and the other conditional index solution.  I see how these would work well.

       

      However the issue I'm facing is that my sequence depends on a change in both reference and time.  I want to count a change only when both have changed. 

       

      My sequence column is calculated thus:

       

      Sequence by Reference = 
VAR CURRENTDATE = 'BR 2018 Washing Timelines'[Date Début Lavage]

RETURN COUNTROWS (
    FILTER (
        CALCULATETABLE (
            'BR 2018 Washing Timelines',
            ALLEXCEPT ( 'BR 2018 Washing Timelines', 'BR 2018 Washing Timelines'[Référence] )
        ),
        'BR 2018 Washing Timelines'[Start Date] < CURRENTDATE
          || ( 'BR 2018 Washing Timelines'[Start Date] = CURRENTDATE
    )
))

      based on Marco Russo's code

       

      I note that your suggestion includes the use of Earlier (which Marco also suggests where VAR is not an option).  Do you think I should use Earlier in my case (I'm not at all familiar with how it works) would it pick up the change in time and reference?

       

      I will try later today and what result it gives me.

       

      Thanks again for your help.

      zedleb

      • zedleb's avatar
        zedleb
        Frequent Visitor
        8 years ago

        v-chuncz-msft

         

        THANK YOU :smileyhappy:

         

        Your code worked perfectly!

         

        A very happy zedleb (after one solid week of trying to solve this!)

        How do I mark as solved...?