Using DirectQuery, Create a Measure to Lookup Value from Column and Place in Matrix

[patri0t82]

Hello,

Thank you to anyone in advance for your assistance.

I have a table with Dates and Shifts (A, B, C, D shift).

I would like to lookup the date from a matrix, as seen in second image, in the Table "ShiftRotation", ShiftDate column and somehow in the header columns below 1,2,3,4,5, etc. show whether it's D or N, based on Day or Night, based on the SELECTEDVALUE(Shift[Shifts])

 I was thinking something along the lines of Switch, but have no idea how to incorporate it into the Matrix.

[patri0t82]

I was thinking something along the lines of:

Measure2 = SELECTEDVALUE(CalendarTable[Day]) & 
LOOKUPVALUE(Switch(True(),"A Shift","A_Shift","B Shift","B_Shift","C Shift", "C_Shift", "D Shift", "D_Shift"),ShiftRotation[ShiftDate],SELECTEDVALUE(CalendarTable[Date]))

..but laughably, I really don't know how to use switch properly.

Then instead of using CalendarTable[Day] for my columns I would use Measure2, and it would give me something like

1D, 2D, 3N, 4N, 5N    6    7   8   9   10D, 11D, 12D, 13N, 14N    etc.

[v-yanjiang-msft]

Hi @patri0t82 ,

Sorry I'm not very clear about your data structure, what's in the Calendar table, also, the Name column in your snapshot is a column in which table? Could you please clerify about the data structure?

Best Regards,
Community Support Team _ kalyj

[patri0t82]

Hello, thanks for the reply. Please forgive the lack of clarity.

The [Name] column is in the ShiftSchedule table, where all the records are stored, indicating that someone is scheduled to work.

CalendarTable is an autogenerated table which bases its start and end dates off of the dates in ShiftSchedule. 

I have a relationship between CalendarTable[Date] and ShiftSchedule[ScheduleDate] and CalendarTable[Date] and ShiftRotation[ShiftDate]

[patri0t82 · Accepted solution]

Hello, I have arrived at a solution. I will create a secondary Matrix above and use values as Rows, then line one matrix above the other.

This is the code. Thank you again for your help.

DayNight = 
SWITCH (
        TRUE,
        SELECTEDVALUE ( Shift[Shifts] ) = "A Shift",
            LEFT(LOOKUPVALUE (
                ShiftRotation[A_Shift],
                ShiftRotation[ShiftDate], SELECTEDVALUE ( CalendarTable[Date] ) ),1)
            ,
        SELECTEDVALUE ( Shift[Shifts] ) = "B Shift",
            LEFT(LOOKUPVALUE (
                ShiftRotation[B_Shift],
                ShiftRotation[ShiftDate], SELECTEDVALUE ( CalendarTable[Date] ) ),1)
            ,
        SELECTEDVALUE ( Shift[Shifts] ) = "C Shift",
            LEFT(LOOKUPVALUE (
                ShiftRotation[C_Shift],
                ShiftRotation[ShiftDate], SELECTEDVALUE ( CalendarTable[Date] ) ),1)
            ,
        SELECTEDVALUE ( Shift[Shifts] ) = "D Shift",
            LEFT(LOOKUPVALUE (
                ShiftRotation[D_Shift],
                ShiftRotation[ShiftDate], SELECTEDVALUE ( CalendarTable[Date] ) ),1)
            )