3 way dependency

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.