Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I need to implement a DAX measure (call it myMeasure) based on the algorithm that follows.
I have a table with records/events containing eventStart & eventEnd fields with the respective time stamps.
I also need a temporary list (call it myList) containing time stamps.
The Algorithm:
1 Sort records/events by eventStart
For the first record/event:
2 myMeasure = 1
3 store the value of field eventEnd as the first element of myList.
For each additional record/event:
4 If eventStart <= minimum value stored in myList
5 myMeasure = len(myList) + 1
6 append the value of field eventEnd in myList
7 else
8 find the element in myList with the minimum value (call it Nth element)
9 myMeasure = N
10 replace the value stored in the Nth element of the list with the value of field eventEnd
Note: I came up with the above in order to address the 3rd bullet in this post. If there is another algorithm/implementation, more suitable for DAX, I will gladly follow it.
Thank you!
Hi @cgi ,
I think the point is create an index column after sort records by eventStart.
Please refer to the measure below and see if it makes any sense.
Measure =
var mylist = CALCULATETABLE(VALUES('Table'[eventEnd]),FILTER(ALLSELECTED('Table'),'Table'[Index]<MAX('Table'[Index])))
var minlist = IF(SELECTEDVALUE('Table'[Index])=1,SELECTEDVALUE('Table'[eventEnd]),CALCULATE(MIN('Table'[eventEnd]),FILTER(ALLSELECTED('Table'),'Table'[Index]<MAX('Table'[Index]))))
return
IF(SELECTEDVALUE('Table'[Index])=1,1,IF(SELECTEDVALUE('Table'[eventStart])<=minlist,COUNTROWS(mylist)+1,CALCULATE(MAX('Table'[Index]),mylist)))
Best Regards,
Jay
Community Support Team _ Jay Wang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @v-jayw-msft
Thanks for the reply. I tried your measure, and I found that it works for “nicely staggered” events (example 1 below) but breaks down in the other cases.
Also, please bear in mind that the table of events is a result of slicing and filtering, so the sorting and indexing must be done within the measure.
In addition, I expect myList to be rebuild—because of step 10—in each myMeasure calculation (by parsing every time all the events) since it cannot be “preserved” from one measure calculation to the next (right?).
BR
Hi @cgi ,
The measure is only for reference, provide an idea. You should modify the measure according to your fact table. Looks like i have missed the step 10, much apologizes. It would be better if you can share some sample data or your Pbix to us if you don't have any Confidential Information.
Best Regards,
Jay
Community Support Team _ Jay Wang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
I prepared a file with some data. Hope it helps.
https://ln2.sync.com/dl/1f5fa0030/3immsut4-mesmjwbq-qy8vyn6y-j87yjt6d
Hi @cgi ,
The link you provide comes out an error like below.
https://ln2.sync.com/dl/1f5fa0030/3immsut4-mesmjwbq-qy8vyn6y-j87yjt6d%20.
Best Regards,
Jay
Community Support Team _ Jay Wang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
I am sorry about this. Can you please try again with the following? Is working on my side.
https://ln2.sync.com/dl/1f5fa0030/3immsut4-mesmjwbq-qy8vyn6y-j87yjt6d
Thank you.
Hi @cgi ,
I can see the file now but i can't download it this time.
No matter i click the Download option at center or at top right, it just refreshed the page but didn't download the file.
I also checked my download list and i'm sure i didn't download the file.
Anyway, do you have any other ways to share such as OneDrive for Business?
Best Regards,
Jay
Community Support Team _ Jay Wang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Let's give it another try!
https://1drv.ms/u/s!Av1_xIrJpxNMgSytwdjsIfXPjqH_
Apologies again, and thanks for your time.
Hello @v-jayw-msft
Thank you for the reply!
I will gladly share data—will need some time to anonymize them, though.
If I may, however, I believe the problem is quite easy to stipulate. Let me give an example:
And keep in mind, that even if we can calculate the measure, I still have to manage to use it as a “Task” in the Gantt chart visual, hopefully using the disconnected table “trick”!
Another note: I have found that the TimeLine Storyteller custom visual (from Microsoft) does precisely that! The only problem is, it does not seem to display events shorter than an hour (bug?/feature?). See here.
Thanks again for your time!
BR
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
135 | |
120 | |
101 | |
71 | |
61 |