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

ValtteriN's avatar
ValtteriN
Icon for Community Champion rankCommunity Champion
2 years ago

Hi,

If you want to do this in dax something like this should do:

Measure 28 = CALCULATE(MAX('Table (39)'[Productivity]),ALL('Table (39)'[Shift Name]))

Here we are not "copying" the value, but instead removing the shift from consideration with ALL. This way we take the maximum productivity and apply that to all the values.


End result:

 

Here Dave Jones 3/2/2024 keeps the value 422 as expected.

I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!

My LinkedIn: https://www.linkedin.com/in/n%C3%A4ttiahov-00001/