Forum Discussion

Gewoodruff's avatar
Gewoodruff
Frequent Visitor
5 years ago
Solved

Data Slicer for Active Employees

Hello,   I have a list of employees, some active and some terminated. I have created a measure to show me which employees are active.    What I would like to do now is be able to use a date slice...
Need Help
  • Jihwan_Kim's avatar
    Jihwan_Kim
    5 years ago

    Hi, Gewoodruff 

    Please check the below picture and the sample pbix file, whether it is what you are looking for.

    I tried to create a sample pbix file based on the explanation.

    I prefer to have inactive relationships like below if a fact table has more than two date-related columns.

     

     

    Active Employees Count =
    CALCULATE (
    COUNTROWS ( Employees ),
    FILTER (
    Employees,
    Employees[Startdate] <= MAX ( Dates[Date] )
    && Employees[TerminationDate] >= MIN ( Dates[Date] )
    )
    )
     
     
     

    Hi, My name is Jihwan Kim.

     

    If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

     

    Linkedin: linkedin.com/in/jihwankim1975/

    Twitter: twitter.com/Jihwan_JHKIM

Jihwan_Kim's avatar
Jihwan_Kim
Icon for Super User rankSuper User
5 years ago

Hi, Gewoodruff 

Please check the below picture and the sample pbix file, whether it is what you are looking for.

I tried to create a sample pbix file based on the explanation.

I prefer to have inactive relationships like below if a fact table has more than two date-related columns.

 

 

Active Employees Count =
CALCULATE (
COUNTROWS ( Employees ),
FILTER (
Employees,
Employees[Startdate] <= MAX ( Dates[Date] )
&& Employees[TerminationDate] >= MIN ( Dates[Date] )
)
)
 
 
 

Hi, My name is Jihwan Kim.

 

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

 

Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM

Gewoodruff's avatar
Gewoodruff
Frequent Visitor
5 years ago

Hi Jihwan,

 

Your example works well however I think it might not be working for because i have some blank termination dates. do you have any advice around this?

 

Thanks,

 

Georgia

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Icon for Super User rankSuper User
    5 years ago

    Hi, Gewoodruff 

    Thank you for your feedback.

    In case the termination date is blank, please check the below-amended measure.

     

    Sometimes, I saw that some organizations do not leave it blank, but insert the far far away date, for instance, 2999-Dec-31.

     

     

     

     

    Active Employees Count =
    CALCULATE (
    COUNTROWS ( Employees ),
    FILTER (
    Employees,
    Employees[Startdate] <= MAX ( Dates[Date] )
    && OR (
    Employees[TerminationDate] >= MIN ( Dates[Date] ),
    ISBLANK ( Employees[TerminationDate] )
    )
    )
    )
     
     
     
     

    Hi, My name is Jihwan Kim.


    If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.


    Linkedin: linkedin.com/in/jihwankim1975/

    Twitter: twitter.com/Jihwan_JHKIM