cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
fanicha Frequent Visitor
Frequent Visitor

Unassigned employee slicer based on Start and End date

Hi, I have a table that contains these columns:

 

  1. [Project ID]
  2. [Employee ID]
  3. [Start date]
  4. [End date]

And I would like to create a slicer that can show me all employees that are free (no project assigned) between two dates.

Note: there are several rows for each employee.

 

Thanks in advance!

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Unassigned employee slicer based on Start and End date

Hi,

 

Your method is completely different from mine.  Ihave converted your dataset into a 3 column one using the Query Editor (not by writing a DAX formula in the PowerPivot).  Please review my solution carefully.

 

Here is my PBIX file.

5 REPLIES 5
Moderator v-yuezhe-msft
Moderator

Re: Unassigned employee slicer based on Start and End date

@fanicha,

Could you please share dummy data of your table?Do you have blank project ID values for some employees in the table? Do you want to create slicer using date field?

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
fanicha Frequent Visitor
Frequent Visitor

Re: Unassigned employee slicer based on Start and End date

@v-yuezhe-msft,

 

All employees have non-blank Project ID.

 

projecttable.PNG

Yes, I would like a date slicer, ideally, the one where you can pick dates between, like this:

 

slicer.PNG

 

Here's a sample of my data:

 

https://www.dropbox.com/s/a32mjs75swy0web/Sample_assignments.pbix?dl=0

 

I think I need to create a separate calendar table with all the employees that are not assigned, but I don't know how to do that from the table that I have.

 

Regards,

Estefania

Super User
Super User

Re: Unassigned employee slicer based on Start and End date

Hi,

 

You may refer to my solution in this Excel workbook.  The same Excel workbook can either be built from scratch in PowerBi desktop or you may also import it there.  Just go to File > Import in PowerBI dekstop

 

Hope this helps.

 

Untitled.png

fanicha Frequent Visitor
Frequent Visitor

Re: Unassigned employee slicer based on Start and End date

@Ashish_Mathur,

 

I imported the excel workbook perfectly. I tried to build it from scratch in Power BI, but I'm not sure what I did wrong while to building this table:

datatable.PNG

 

What I did was create a Calendar Table called "Bridge". I also created an additional table like the one above with this formula:

 

WorkSchedule = 
SUMMARIZE (
    GENERATE (
        Assignments,
        CALCULATETABLE (
            VALUES ( Bridge[Date] ),
            DATESBETWEEN ( Bridge[Date], Assignments[StartDate], Assignments[EndDate] )
        )
    ),
    Bridge[Date],
    Assignments[Employee],
    Assignments[Proyect]
)

And creating a Measure in that table:

 

Employees on bench = IF(ISBLANK(DISTINCTCOUNT(WorkSchedule[Employee])),1,BLANK())

But I can't make a relation between the Calendar Table Bridge and WorkSchedule, therefore, I can't use the dates in bridges as a slicer.

 

Find attached the .pbix:

 

https://www.dropbox.com/s/1gcugvxyle1r3es/Sample_assignments_second_try.pbix?dl=0

 

Any suggestion?

 

Thanks a lot!

 

Regards,

Super User
Super User

Re: Unassigned employee slicer based on Start and End date

Hi,

 

Your method is completely different from mine.  Ihave converted your dataset into a 3 column one using the Query Editor (not by writing a DAX formula in the PowerPivot).  Please review my solution carefully.

 

Here is my PBIX file.

Helpful resources

Announcements
GregDeckler

How to Get Your Question Answered Quickly

Power BI Super User, Greg Deckler, explains

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Top Ideas
Users Online
Currently online: 34 members 989 guests
Please welcome our newest community members: