Forum Discussion

lozg's avatar
lozg
Regular Visitor
4 years ago
Solved

Visualisations with slicers determined by specific date range - active employees

Hi there, I'm a pretty advanced Excel user, though very new to Power BI. I've got some employee data and am trying to create an admin user overview dashboard to show the number of joiners, leavers,...
Need Help
  • vanessafvg's avatar
    vanessafvg
    4 years ago

    I have created one example for you see attached.

     

    As you have more than one relationship on the date, you need a primary and inactive relationship, termination date is the inactive and you will see for the measure i use the userelationship function.    I used countrows(employee) to count but you can just as well use distinctcount(employeeid) or whatever it is.

vanessafvg's avatar
vanessafvg
Community Champion
4 years ago

I have created one example for you see attached.

 

As you have more than one relationship on the date, you need a primary and inactive relationship, termination date is the inactive and you will see for the measure i use the userelationship function.    I used countrows(employee) to count but you can just as well use distinctcount(employeeid) or whatever it is.

lozg's avatar
lozg
Regular Visitor
4 years ago

Thank you so much for this, Vanessa!
Very helpful to guide in the right direction.
I've managed to use this as the basis to make some of the other cards.
The one I'm struggling to make is the 'Voluntary' terminations the current year.
I tried adpating the Measure to:

var mindate = DATE(YEAR(TODAY()),1,1)
var maxdate = DATE(YEAR(TODAY()),12,31)
var cnt  =  CALCULATE(COUNTROWS(Data),Filter(Data,Data[Leave Reason]="Voluntary") , DATESBETWEEN('Calendar'[Date], mindate, maxdate), USERELATIONSHIP('Calendar'[Date], Data[Termination Date]))
return cnt
This works perfectly for terminations without specifying the reason. However, when trying to specifiy the 'Voluntary' leavers in this way (specifically the Filter(Data,Data[Leave Reason]="Voluntary") element) it comes back blank.
Could you please help correct what I did wrong when trying to add a condition of 'Leave Reason' to the measure?
  • vanessafvg's avatar
    vanessafvg
    Community Champion
    4 years ago

    will have a look at bit later and get back to you.