Forum Discussion

Thomas_MedOne's avatar
Thomas_MedOne
Icon for Helper III rankHelper III
2 years ago
Solved

Return value from row/column based on another row/column

I haven't seen this exact scenario or even something close to it so I'm hoping someone here can help.  I have a table with several columns, these are the relevant ones below.     A person works a s...
  • Thomas_MedOne's avatar
    Thomas_MedOne
    2 years ago

    I cleaned it up a bit to look like this:

     

    FinalProd =
    VAR Shift1 = LOOKUPVALUE('Schedule'[Productivity],'Schedule'[Shift Date],'Schedule'[Shift Date],'Schedule'[POSITION NAME],'Schedule'[POSITION NAME],'Schedule'[Shift Name],"7-4 Shift")
    VAR Shift2 = LOOKUPVALUE('Schedule'[Productivity],'Schedule'[Shift Date],'Schedule'[Shift Date],'Schedule'[POSITION NAME],'Schedule'[POSITION NAME],'Schedule'[Shift Name],"7-4 Shift 2")
    VAR Shift3 = LOOKUPVALUE('Schedule'[Productivity],'Schedule'[Shift Date],'Schedule'[Shift Date],'Schedule'[POSITION NAME],'Schedule'[POSITION NAME],'Schedule'[Shift Name],"7-4 Shift 3")

    RETURN If ('Schedule'[Shift Name] = "AMC Shift",
    If (Shift1,Shift1,
    If(Shift2,Shift2, Shift3)),
    'Schedule'[Productivity])

Thomas_MedOne's avatar
Thomas_MedOne
Icon for Helper III rankHelper III
2 years ago

I cleaned it up a bit to look like this:

 

FinalProd =
VAR Shift1 = LOOKUPVALUE('Schedule'[Productivity],'Schedule'[Shift Date],'Schedule'[Shift Date],'Schedule'[POSITION NAME],'Schedule'[POSITION NAME],'Schedule'[Shift Name],"7-4 Shift")
VAR Shift2 = LOOKUPVALUE('Schedule'[Productivity],'Schedule'[Shift Date],'Schedule'[Shift Date],'Schedule'[POSITION NAME],'Schedule'[POSITION NAME],'Schedule'[Shift Name],"7-4 Shift 2")
VAR Shift3 = LOOKUPVALUE('Schedule'[Productivity],'Schedule'[Shift Date],'Schedule'[Shift Date],'Schedule'[POSITION NAME],'Schedule'[POSITION NAME],'Schedule'[Shift Name],"7-4 Shift 3")

RETURN If ('Schedule'[Shift Name] = "AMC Shift",
If (Shift1,Shift1,
If(Shift2,Shift2, Shift3)),
'Schedule'[Productivity])