Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I have a table which contains a record for every week end date for every week that each employee is on a project and the employee's employee ID and project ID. For example, if the employee is assigned to work on that project for the next 3 months, there will be a record for them for every week end date within that period associated with their employee ID and the project ID. If the employee is not assigned to work on a project during those weeks, there will be no record for them in the table. This table is linked to an employee dimension table which contains all the employees that work for the company.
What would be the best way in Power BI to create a line chart which displays a count of all the employees not working on a project for every week end date and make it so that you are able to get a list of those employees who are not on a project for a given week?
You'll want a calendar table to create a relationship for your week end date. You'll want this for your visualization.
Then to get the number employees not in the project table you would do something like
Bench = COUNTROWS( EXCEPT( ALL(EmployeesTab[Employee ID]), VALUES(ProjectTab[Employee ID]) ) )
Map the Date on the X-Axis and Bench as the value.
Hope this helps
David
Tried this, it doesn't seem to work. When you say project table I assume you are referring to the fact table. I just want to clarify the fact table contains a record for every week that each employee is on a project. Basically to create the fact table I joined together the employee dimension table, project dimension table, date dimension table and assignment dimension table. The assignment dimension table contains a record for each assignment, which is associated with an employee, project and start and end date. I joined the date dimension table to the assignment dimension table on date between assignment start date and end date.
Can you share your data model and sample data?
I do not have any sample data but this is the data model.
fact table:
week end date (every week between assignment start and end date for each employee on a project)
assignment start date
assignment end date
assignment id
employee id
project id
assignment table:
assignment id
employee id
assignment start date
assignment end date
etc/
Employee table:
employee id
first name
last name
etc.
project table:
project id
projetc name
project type
etc.
The way you have described this, assignment really isn't a dimension table per se. In PowerBI, you can't have a 1-to-many relationship between "assignment" and "fact" with a multiple-column key like you are showing here.
If you had a proper DATE table, you would not need a table showing every week end date in between assignment start and assignment end because that could be calculated on the fly in your measures and/or visualizations. I will try to throw together an example to illustrate what I'm talking about.
Hi @Anonymous - sorry it has taken so long to get back to you. I haven't fully fleshed out the solution, but I hope this at least gives you a kickstart.
Model:
Data:
IsWeekend calculated column on DateTab
IsWeekend = if(WEEKDAY(DateTab[Date])=6, TRUE(), FALSE())
Measure for number of employees assigned at a given date:
Assigned = SUMX ( FILTER ( DateTab, DateTab[IsWeekend] = TRUE () ), CALCULATE ( COUNTA( Assignment[Employee ID]), ALL(Assignment), FILTER(Assignment, Assignment[Assignment Start Dt] < DateTab[Date] && Assignment[Assignment End Dt] >= DateTab[Date]) ) )
Then number of unassigned employees is simply the count of employees minus the assigned employees
Unassigned = IF([Assigned]>0, COUNTROWS(ALL(Employee)) - [Assigned])
Then a visual
Hope this helps
David
Hi @Anonymous,
Have you tried the solution provided by @dedelman_clng above? Does it help in your scenario? If the solution helps, could you accept it as solution to close this thread?
If you still have any question on this issue, feel free to post here.
Regards
User | Count |
---|---|
125 | |
106 | |
99 | |
63 | |
62 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |