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

The 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.

Reply
roninn7503
New Member

3 way dependency

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")

 

 

2 ACCEPTED SOLUTIONS
DataNinja777
Super User
Super User

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,

View solution in original post

v-menakakota
Community Support
Community Support

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.

View solution in original post

4 REPLIES 4
v-menakakota
Community Support
Community Support

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.

v-menakakota
Community Support
Community Support

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.

DataNinja777
Super User
Super User

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,

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Top Solution Authors
Top Kudoed Authors