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/

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

that doesn't seem to be working for me. It's outputting zero.

 

This is not as elegant at all but I did find a way to do it by creating a new column. It looks like this:  there are three potential shifts to match with the name and the date. So, I first see if it's the AMC shift, then I search for the other shifts that could have the same prod. If they contain data, then return it, if not, for all three, just copy the prod from the prod field.  that is working for me.

 

FinalProd = If ('Schedule'[Shift Name] = "AMC Shift",
If (LOOKUPVALUE('Schedule'[Productivity],'Schedule'[Shift Date],'Schedule'[Shift Date],'Schedule'[POSITION NAME],'Schedule'[POSITION NAME],'Schedule'[Shift Name],"7-4 Shift"),
LOOKUPVALUE('Schedule'[Productivity],'Schedule'[Shift Date],'Schedule'[Shift Date],'Schedule'[POSITION NAME],'Schedule'[POSITION NAME],'Schedule'[Shift Name],"7-4 Shift"),
If(LOOKUPVALUE('Schedule'[Productivity],'Schedule'[Shift Date],'Schedule'[Shift Date],'Schedule'[POSITION NAME],'Schedule'[POSITION NAME],'Schedule'[Shift Name],"7-4 Shift 2"),
LOOKUPVALUE('Schedule'[Productivity],'Schedule'[Shift Date],'Schedule'[Shift Date],'Schedule'[POSITION NAME],'Schedule'[POSITION NAME],'Schedule'[Shift Name],"7-4 Shift 2"),
LOOKUPVALUE('Schedule'[Productivity],'Schedule'[Shift Date],'Schedule'[Shift Date],'Schedule'[POSITION NAME],'Schedule'[POSITION NAME],'Schedule'[Shift Name],"7-4 Shift 3"))),
'Schedule'[Productivity])