Forum Discussion
Creating a 1 to Many relationship for shift working in Power BI
Hi
I am trying to create a 1 to many relationship between my Shifts table and my Date table. When two Officers work on the same day they are assigned either a Day or Late Shift. The Day shift is from 7:30 - 16:30 and the Late shift is from 12:00 - 21:00.
Officers will hand over the Asset ID (V0001) at 12:30 if they share a day and late shift on the same day.
Any ideas on how I can do this in Power BI please? I have attached a sample shifts table below.
Thanks
| Officer | Shift Date | Shift Type | Asset ID |
| Officer 1 | 1/2/2022 | Day | V0001 |
| Officer 2 | 1/2/2022 | Late | V0001 |
| Officer 1 | 2/2/2022 | Day | V0001 |
| Officer 1 | 3/2/2022 | Late | V0001 |
| Officer 2 | 4/2/2022 | Day | V0001 |
| Officer 2 | 4/2/2022 | Late | V0001 |
| Officer 2 | 5/2/2022 | Late | V0001 |
Hi Anonymous ,
What is your desired output? Is it trying to calculate the [Asset ID] column? If so, then why is the value of these rows "V0001" and what is the basis for the calculation?
If not, then is creating the Dates table what you want?
Dates = CALENDAR ( DATE ( 2022, 1, 1 ), DATE ( 2022, 12, 31 ) )Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
1 Reply
v-kkf-msftCommunity Support
Hi Anonymous ,
What is your desired output? Is it trying to calculate the [Asset ID] column? If so, then why is the value of these rows "V0001" and what is the basis for the calculation?
If not, then is creating the Dates table what you want?
Dates = CALENDAR ( DATE ( 2022, 1, 1 ), DATE ( 2022, 12, 31 ) )Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.