Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
You may take a look at the following post.
You may take a look at the following post.
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
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...?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
94 | |
80 | |
67 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |