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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
cgi
Frequent Visitor

DAX measure for events

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!

9 REPLIES 9
v-jayw-msft
Community Support
Community Support

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.

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

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

1.png

 

2.png

 

3.png

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.

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

@v-jayw-msft ,

 

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.

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

@v-jayw-msft 

 

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.

12.PNG

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.

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

@v-jayw-msft 

 

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:

  • Imagine you have hotel rooms numbered 1, 2, … and guests staying in them for a limited duration.
  • When a guest arrives, you place them in the empty room with LOWEST room number.
  • So, you can always calculate which room an arbitrary guest stayed by merely knowing the arrival and departure times of all previous guests.
  • How do you figure out THAT room for ALL the guests?

 

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.