How to filter data on table without a relationship

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)

Hi @fjoines,

Due to inactivity, we’d like to conclude that your inquiry was out of the experts and users’ bounds.

If you were sure you provided all pertinent context to your concerns and read how to use the forum more effectively and still find your question unanswered, your membership also comes with relevant resources that may help you with your Power BI education, so we advise that you check these resources as well.

While our users and experts do as much as reasonable to help you with your inquiries, not all concerns can be attended to especially if there are some learnings to be done. Thank you!

1 Like