Forum Discussion
How to determine which is the current record between multiple records for an employee
Super UserAnonymous assuming you have a date dimension in your model that you will use for the slicer, if not then you can add one using my blog post here Create a basic Date table in your data model for Time Intelligence calculations | PeryTUS IT Solutions
Create a new measure to filter employees that falls in the selected date range:
Filter Employee =
VAR __startDate = MIN ( 'Date'[Date] )
VAR __endDate = MAX ( 'Date'[Date] )
RETURN
CALCULATE (
COUNTROWS ( Emp ),
KEEPFILTERS (
Emp[Position start date] >= __startDate &&
Emp[Position end date] <= __endDate &&
NOT ISBLANK ( Emp[Position end date] )
)
)
Create a table visual, add all the columns you want in the visual, add a visual level filter, and add above measure and where value = 1 and that will do it.
✨ Follow us on LinkedIn
Learn about conditional formatting at Microsoft Reactor
My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!
⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡
Thanks so much Parry2k for your help with this
I am running into a couple of issues
- My employee table with an employee number, position start date and position end date is called Sheet1
- My date table dimension table: [date] date table
Scenario
- Every employee has a position record with a start date
- Some employees may have multiple position records over time
- No two-position records per an employee can have an overlapping start date
- I need to identify using a data slicer with month and years (from date table) which is the active position record for the employee at a moment in time so I can only ever see a maximum of one position record per an employee
- I am not sure how to do this - I am trying to identify one active position record amongst multiple inactive and (1) active postion per an employee
I guess I am trying to identify the max record at a moment in time whether in the past or present.