Prepare Staff Roster Data for PowerBI Analysis

HI Hesham121 

In Power Query, highlight the Emps Table. Then highlight the Date columns, right click and the unpivot these columns. You will then have two new columns, Attribute and Value. Rename Attribute to Working Date and Value to Task. This will give you a table format that Power BI can work properly with.

Can i ask why the Public Holiday table is seperate to the Date? If there are no duplicate dates in the table, in Power query merge it with the Date table on the date and expand to show the Holiday name. 

To count how many people where doing a certain task, please try the following

Tasks Amount = COUNT(Emps[Task])

Here in a Tbale or Matrix visual you could add the Task, Employee name & Date to the table and then this measure and you will get the reults needed.

if you want a calculation for a particular Task or Tasks you can do the following

R tasks = CALCULATE([Task Amount], KEEPFILTERS(Emps[Task] = "R"))

R-D-N Tasks = CALCULATE([Total Task] in {"R", "D", "N"}))

Thanks

Joe

If this post helps, then please Accept it as the solution