Prepare Staff Roster Data for PowerBI Analysis

[Hesham121]

Hello,

I've Staff Roster as shown in the below screenshot and I need to build a data model to analyze the data to get for example, how many employees was doing "R" in specific date... 

Noting that I'll update the Rosters per month by adding employees' activities per day with the same below format

I've built some data models as below, but it doesn't give me the expected results

[Hesham121]

Thank you so much for the detailed solution, I'm new to PowerBI and it was clear for me!

[JoeBarry · Accepted solution]

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