Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
zedleb
Frequent Visitor

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.

asTimelineVis.png

 

 

 

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.

 asTimelineData.png

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

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

 

1 ACCEPTED SOLUTION
v-chuncz-msft
Community Support
Community Support

@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...

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

3 REPLIES 3
v-chuncz-msft
Community Support
Community Support

@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...

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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
Frequent Visitor

@v-chuncz-msft

 

THANK YOU Smiley Happy

 

Your code worked perfectly!

 

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

How do I mark as solved...?

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.