Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

What would be the best way to accomplish this in Power BI?

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?

7 REPLIES 7
dedelman_clng
Community Champion
Community Champion

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

Anonymous
Not applicable

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?

Anonymous
Not applicable

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:

model.PNG

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Data:

 

emp.PNG

 

 

 

 

 

 

 

 

proj.PNG

 

 

 

 

 

 

assn.PNG

 

 

 

 

 

 

 

 

 

 

 

 

 

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

graph.PNG

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

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. Smiley Happy

 

Regards

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.