Count changes in a sequence; as.Timeline visualization

[zedleb]

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

[v-chuncz-msft · Accepted solution]

@zedleb,

You may take a look at the following post.

http://community.powerbi.com/t5/Desktop/How-to-create-ID-variable-by-value-an-other-variable/m-p/369...

[zedleb]

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]

@v-chuncz-msft

THANK YOU 

Your code worked perfectly!

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

How do I mark as solved...?