Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
good day, i have 3 tables. I wish to calculate the time difference between table 1 and 3.
Table 1: ir_root_subset with primary key OBJECT_NUMBER
Table 2: msr_details PARENT_NUMBER = ir_root_subset OBJECT_NUMBER
Table 3: root_file OBJECT_NUMBER = msr_details PARENT_NUMBER
The time fields are:
ir_root_subset: IR_STATUS_OPEN
root_file: SR_STATUS_NEW
Please assist with the code to create a calculated column. I've tried:
TD_Column =
VAR IRTime = LOOKUPVALUE(
ir_root_subset[IR_STATUS_OPEN],
ir_root_subset[OBJECT_NUMBER],
LOOKUPVALUE(
msr_details[OBJECT_NUMBER],
msr_details[PARENT_NUMBER],
root_file[PARENT_NUMBER]
)
)
VAR SRTime = root_file[SR_STATUS_NEW]
VAR DiffSeconds = DATEDIFF(IRTime, SRTime, SECOND)
RETURN
FORMAT(INT(DiffSeconds/3600), "00") & ":" &
FORMAT(INT(MOD(DiffSeconds, 3600)/60), "00") & ":" &
FORMAT(MOD(DiffSeconds, 60), "00")
Solved! Go to Solution.
Hi @roninn7503 ,
Your approach is correct in structure, but the nested LOOKUPVALUE might not be retrieving the expected results due to multiple matches or missing values. You can restructure it for better clarity and ensure proper handling of BLANK() values.
TD_Column =
VAR IRTime =
LOOKUPVALUE(
ir_root_subset[IR_STATUS_OPEN],
ir_root_subset[OBJECT_NUMBER],
LOOKUPVALUE(
msr_details[PARENT_NUMBER],
msr_details[OBJECT_NUMBER],
root_file[OBJECT_NUMBER]
)
)
VAR SRTime = root_file[SR_STATUS_NEW]
VAR DiffSeconds =
IF(
NOT(ISBLANK(IRTime)) && NOT(ISBLANK(SRTime)),
DATEDIFF(IRTime, SRTime, SECOND),
BLANK()
)
RETURN
IF(
ISBLANK(DiffSeconds),
BLANK(),
FORMAT(INT(DiffSeconds/3600), "00") & ":" &
FORMAT(INT(MOD(DiffSeconds, 3600)/60), "00") & ":" &
FORMAT(MOD(DiffSeconds, 60), "00")
)
This version ensures that LOOKUPVALUE correctly retrieves IR_STATUS_OPEN by resolving PARENT_NUMBER mappings in a stepwise manner. It also includes a check to prevent errors when IRTime or SRTime is blank. If DiffSeconds evaluates to BLANK(), the measure returns BLANK() instead of attempting to format an invalid value. If the column still returns blanks, verify that OBJECT_NUMBER and PARENT_NUMBER correctly match across tables and that LOOKUPVALUE is fetching expected results by testing each part separately. Also, ensure IR_STATUS_OPEN and SR_STATUS_NEW are stored as datetime values in Power BI.
Best regards,
Hi @roninn7503 ,
I hope this information is helpful. Please let me know if you have any further questions or if you'd like to discuss this further. If this answers your question, please Accept it as a solution and give it a 'Kudos' so others can find it easily.
Thank you.
Hi @roninn7503 ,
I hope this information is helpful. Please let me know if you have any further questions or if you'd like to discuss this further. If this answers your question, please Accept it as a solution and give it a 'Kudos' so others can find it easily.
Thank you.
Hi @roninn7503 ,
May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.
Thank you.
Hi @roninn7503 ,
Thank you for sharing your query! We appreciate the detailed response provided by @DataNinja777 their approach effectively handles potential issues with lookupvalue and ensures proper error handling.
If you’re still facing issues, I recommend trying an alternative approach using calculate with filter instead of lookupvalue. This can provide more control over retrieving values, especially if multiple records exist for the same key. Additionally, please verify if object_number and parent_number have unique mappings across tables to avoid unexpected results.
If this post was helpful, please give us Kudos and consider marking Accept as solution to assist other members in finding it more easily.
Regards,
Menaka.
Hi @roninn7503 ,
Your approach is correct in structure, but the nested LOOKUPVALUE might not be retrieving the expected results due to multiple matches or missing values. You can restructure it for better clarity and ensure proper handling of BLANK() values.
TD_Column =
VAR IRTime =
LOOKUPVALUE(
ir_root_subset[IR_STATUS_OPEN],
ir_root_subset[OBJECT_NUMBER],
LOOKUPVALUE(
msr_details[PARENT_NUMBER],
msr_details[OBJECT_NUMBER],
root_file[OBJECT_NUMBER]
)
)
VAR SRTime = root_file[SR_STATUS_NEW]
VAR DiffSeconds =
IF(
NOT(ISBLANK(IRTime)) && NOT(ISBLANK(SRTime)),
DATEDIFF(IRTime, SRTime, SECOND),
BLANK()
)
RETURN
IF(
ISBLANK(DiffSeconds),
BLANK(),
FORMAT(INT(DiffSeconds/3600), "00") & ":" &
FORMAT(INT(MOD(DiffSeconds, 3600)/60), "00") & ":" &
FORMAT(MOD(DiffSeconds, 60), "00")
)
This version ensures that LOOKUPVALUE correctly retrieves IR_STATUS_OPEN by resolving PARENT_NUMBER mappings in a stepwise manner. It also includes a check to prevent errors when IRTime or SRTime is blank. If DiffSeconds evaluates to BLANK(), the measure returns BLANK() instead of attempting to format an invalid value. If the column still returns blanks, verify that OBJECT_NUMBER and PARENT_NUMBER correctly match across tables and that LOOKUPVALUE is fetching expected results by testing each part separately. Also, ensure IR_STATUS_OPEN and SR_STATUS_NEW are stored as datetime values in Power BI.
Best regards,