Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

How to determine which is the current record between multiple records for an employee

I need to show which record is the 'active' record when using a dynamic date slicer   No two records for an employee overlap position starts    Say I had a date with a date range of April 2012 to...
Need Help
parry2k's avatar
parry2k
Icon for Super User rankSuper User
4 years ago

Anonymous try this:

 

Filter Employee = 
VAR __startDate = MIN ( 'Date'[Date] )
VAR __endDate = MAX ( 'Date'[Date] )
RETURN
CALCULATE (
    COUNTROWS ( Emp ),

    KEEPFILTERS ( 
        Emp[Position start date] >= __startDate
    ),
    KEEPFILTERS (
        Emp[Position end date] <= __endDate
   ),
   KEEPFILTERS (
        NOT ISBLANK ( Emp[Position end date] ) 
    )

)

 

✨ 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.⚡

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Thanks this seems to be operating, however, runs into an error:

     

    !A circulate dependency was detected: PBIPND_vwPosition_Private[Column], PBIND_vwPosition_Private[Current FTE], PBIPND_vwPosition_Private[Column].

     

    A circular dependencycy was detected:

    • PBIPND_vwPosition_Private[Column],
    • PBIPND_vwPosition_Private[Current FTE],
    • PBIPND_vwPosition_Private[Column].

     

    A column from the Position vw is getting picked up? Actual FTE is a decimal type column