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

[Thomas_MedOne]

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 shift and he gets paid productivity for the sales in that shift. But they may actually work an "Add on" Shift which carries additional responsibilities. But productivity is assigned to the original shift he is in so productivity in the add-on shift is blank.

What I would like to figure out is can I copy the productvity of the other shift IF he has worked it on the same day. So the data looks like this: (Notice there can be two different types of shifts that day.

Person	Shift Date	Shift Name	Location	Productivity
Dave Jones	3/1/2024	AMC Shift	MH	0
Dave Jones	3/1/2024	7-4 Shift	MH	445
Mark Castle	3/2/2024	AMC Shift	MH	0
Mark Castle	3/2/2024	7-4 Shift 2	MH	451
Dave Jones	3/2/2024	7-4 Shift	MH	422

Location probably shouldn't factor in. They can work at different locations but it is not likely they will be in the AMC shift at one location and another at the same time. that doesn't happen.

I would like to copy the productivity from the 7-4 shift to the AMC shift when they work the same day. Of course, Dave Jones 3/2 would not copy anywhere because he didn't also work the AMC shift.

[v-yohua-msft]

Hi, @Thomas_MedOne 

Have you solved your problem? If so, can you share your solution here and mark the correct answer as a standard answer to help other members find it faster? Thank you very much for your kind cooperation!

Best Regards

Yongkang Hua

[Thomas_MedOne · Accepted solution]

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]

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]

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