Hello,
I’m trying to create an HR dashboard that can display a snapshot in time. My problem is that while I can create a visual that contains the correct information, the data displayed when drilling thru to the detail is incorrect.
The detail is incorrect because there is no relationship between the date slicer and the table. The date slicer doesn’t have a relationship to the table because the date slicer is used to display employees that were hired on or before the date selected and that are not terminated or were terminated after the date selected.
I have tried building measures using the hire and term date, creating another measure using those measures to determine who is active, and then using that measure as a filter on the table but was not successful.
I have tried create a virtual table to use when displaying the detail but was not successful.
Attached is a simplified pbix file with two slicers; a terminal slicer and the ‘As Of’ slicer. You can see by the measure used in the bar graph that the information is being filtered by the terminal slicer and then the hire and term dates are compared to the selected date. This visual is correct. However, when drilling thru to the data, you will see term dates for employees that were terminated before the date selected which should not be displayed.
I should also note we use an on-prem version of Power BI, not the cloud version. Consequently, we use Power BI Desktop RS which sometimes has different functionality than Power BI Desktop.
I’m not sure how to solve this issue. Any help would be appreciated.
Thank you,
Felicia
test HR dashboard.pbix (390.0 KB)