Make 3 calculated columns 1 column in a different table with date included.

[Anonymous]

Hi, I work at a plant where they work in 3 shifts all connected to a team that cycles every week.

I need to lineup the teams against the shift in the shift table, I made a calculated team table with some help of the community. 

So the W/C is already in, now I need to make a new column in the shift table with the teamcolumns from the team table lined-up. So you get the slicer working you see below. 

Does somebody know how I can do this?

Thanks in advance!

Calculated Team Table:

Shift table W/C:

 

Shifttable shiftName:

Need this slicer as result:

  

[Anonymous · Accepted solution]

Teams =

VAR Shift =

SWITCH(

[shiftName],

"Day", "Day",

"Evening", "Evening",

"Night", "Night",

BLANK()

)

VAR day = [W / C]

VAR NIGHT =

SWITCH(

Shift,

"Day", LOOKUPVALUE('Weekly Shifts'[Day], 'Weekly Shifts'[W / C], day),

"Evening", LOOKUPVALUE('Weekly Shifts'[Evening], 'Weekly Shifts'[W / C], day),

"Night", LOOKUPVALUE('Weekly Shifts'[Night], 'Weekly Shifts'[W / C], day),

BLANK()

)

RETURN

NIGHT

This is the solution

day Night should be: W/C and the data under NIGHT but used it as a quick name.

[Anonymous]

That's not really the question, the thing is that I need to lineup, the teams of the team table, behind the shifts at the shift table. The shifts the teams have are depending on the date, see team table. So wich shift is runned by wich team depends on the date. The team table automatically generates that schedule every week. W/C is the begin of the week.

We need to create a measure that manages this.

[DOLEARY85]

After you have the unpivoted data you'll have day, evening, night in one column (Attribute) - could you then join on the shift table W/C to Team table W/C and shift table ShiftName and Team Table Attribute - using USERELATIONSHIP in DAX? (after creating the relationships in model view)

If I answered your question, please mark my post as solution, Appreciate your Kudos 👍

[DOLEARY85]

Hi,

you could highlight all 3 shift fields and unpivot them in power query

If I answered your question, please mark my post as solution, Appreciate your Kudos 👍