Forum Discussion
Helper INetworkingdays per Employee based on their respective Working Model
The Calendar table is fine as a location for the measure, it doesn't really matter which table you put it in.
I would unpivot the working days table so that there are only 2 columns, Working Model ID and Working Day. There will be multiple rows for each Working Model ID but that is OK.
Helper IHi johnt75 ,
great idea. I converted Calendar with the Bank holidays to true and false. And I converted my WorkingModel Table so that it looks like this - ie. adding 5 columns for the Working days:
WorkingModelID I WD1 I WD2 I WD3 I WD4 IWD 5
Mo-Fr 40hrs I 1 I 2 I3 I 4 I 5
Mo-Fr 30hrs I 1 I 2 I3 I 4 I 5
Mo-Th 20hrs I 1 I 2 I3 I 4
Mo-Tu 16hrs I 1 I 2
Mo-Th 28hrs I 1 I 2 I3 I 4
We 8hrs I 3
Tu,We,Fr 24hrs I 2 I 3 I 5
I inserted the NetWorkingdays Measure in the Calendar table - correct?
I get stuck in in the CALCULATETABLE step - the Workingdays are now in multiple colums, where some entries might be empty. VALUES ('WorkingModels [Workingdays]) is not there anymore.
How do I link in the new table?
Super UserThe Calendar table is fine as a location for the measure, it doesn't really matter which table you put it in.
I would unpivot the working days table so that there are only 2 columns, Working Model ID and Working Day. There will be multiple rows for each Working Model ID but that is OK.
- JKross
Hi johnt75 ,
thank you so much for your feedback! I am getting close! Last thing that remains is that now the Result for a Non-Working Day according to the Working Model is 1 and for a Working Day according to the Working Model is 2.I am checking why that is, do you have any idea?
Worst Case I will substract 1, but I prefer to find the source why it adds 1 Working Day...- johnt75
Can you share a sample PBIX? You can share a link from Google Drive or OneDrive or similar