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
Anonymous
Not applicable

DAX lookup column based on previous id in same table

Hi All,

 

I'm hoping someone can assist with a lookup scenario.

 

I have one table (Image 1) with 2 id columns: op_id, prev_op_id and 2 operational DateTime fields: start_op_dt, end_op_dt.

 

In this image the start_op_dt and end_op_dt are for op_id

 

Image 1

sample 1.jpg

 

 

 

I have to calculate the time difference between the end_op_dt of prev_op_id and start_op_dt of op_id.

 

 

I have tried duplicating the table and linking pre_op_id (duplicated table) to _op_id. Image 2 lists the data of based on duplicating and joing the 2 tables.

 

I then created a DAX to calculate the time difference between end_op_dt and start_op_dt. However each prev_op_id got replicated by the total number of op_ids.

 

sample 2.jpg

 

Is there a way to calculate the time difference within 1 table where op_id where I can search for the end_op_dt for column prev_op_id?

 

Thank you.

 

1 ACCEPTED SOLUTION

Hi @Anonymous,

 

You can also try to use below formula to calculate between current id and previous id.

 

Calculate column:

Diff = DATEDIFF(LOOKUPVALUE([end_op_dt],[op_id],[Prev_op_id]),[start_op_dt],SECOND) 

 

BTW, I think you should switch some correct records, I found your previous end date are greater than current start date.

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

4 REPLIES 4
v-shex-msft
Community Support
Community Support

Hi @Anonymous,

 

I'd like to suggest you add a calculate column to store the diff value between start_date and end_date, then use it to calculate the difference of current row and previous row.

 

Below are sample formulas, you can refer to they if they suitable for your requirement.

 

calculate columns.

 

Diff

Diff = DATEDIFF([start_op_dt],[end_op_dt],SECOND) 

 

 

Diff between current row and previous row.

Diff C - P = [Diff]-LOOKUPVALUE([Diff],[op_id],[Prev_op_id])

 

If you not want to use calculated column, you can try to use follow measure to calculate the diff between current and previous.

Result = MAX([Diff])-LOOKUPVALUE(Table[Diff],Table[op_id],MAX(Table[Prev_op_id]))

 

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
Anonymous
Not applicable

Hi @v-shex-msft,

 

Thanks for your reply.

 

Unfortunately, I was not getting the answer I was hoping for. I was looking to get the difference between the start_op_date for the op_id and the end_op_dt for the prev_op_id.

 

I modified your formula to derive my value:

 

CALCULATE(
 1440 *
 (SUM(Table1[start_op_dt]) -
  CALCULATE(

        (LOOKUPVALUE(Table1[end_op_dt], Table1[op_id], MAX(Table1[prev_op_id])))
 )))

 

However, this measure gave me the result only at an id level, which is fine, but when I visulaised it at a monthly level, I got highly inflated numbers. I was after average for the month. I tried replacing SUM with AVERAGE, however this didn't work.

 

I finally managed to solve it by doing the following:

 

I duplicated the table twice:

1. Just containing op_id and prev_op_id

2. Duplication of the main table

 

I then joined:

* op_id (table 1) to op_id (table 2)

* prev_op_id (table 2) to op_id (table 3)

 

I then created a calculated column:

CALCULATE(IF(
 OR(ISBLANK(SUM(Table3[end_op_dt])),
  ISBLANK(SUM(Table1[start_op_dt])))
  ,BLANK(),
  1440 * (SUM(Table1[start_op_dt]) - SUM(Table3[end_op_dt]))
 ))

 

Finally I changed the default summarisation from Sum to Average.

 

Thanks again for your help. I appreciate it.

Hi @Anonymous,

 

You can also try to use below formula to calculate between current id and previous id.

 

Calculate column:

Diff = DATEDIFF(LOOKUPVALUE([end_op_dt],[op_id],[Prev_op_id]),[start_op_dt],SECOND) 

 

BTW, I think you should switch some correct records, I found your previous end date are greater than current start date.

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
Anonymous
Not applicable

Thanks again for your reply.

 

Apologies for not replying sooner as I have on leave.

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.