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 Anyhow first and foremost is to solve the issue of why my measure is not working in your case?

 What Power BI version you are using? Can you please confirm?

Anonymous's avatar
Anonymous
Not applicable
4 years ago

I have the newest update - version: 2.88.2361.9 64-bit (January 2021)

 

I think it is the syntax for the variable which is incorrect for my situation and I am not replacing them correctly, could you please replace them using the following field and table names I am using? Apologies

 

I am not sure what to put in emp as well

 

Table: PBIPND_vwPositionPrivate

Start date column name: StartDate

End date column name:  EndDate

Employee number column name: StaffNumber

 

Date table name: Date Table

Date column name: Date

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago
     

    Is it because I am referencing two tables

    !! The expression contains multiple columns, but only a single column can be used in a true/false expression that is used as a table filter expression

     

    Filter Employee =

VAR __startDate = MIN ( 'Date Table'[Date] )

VAR __endDate = MAX ( 'Date Table'[Date] )

RETURN

CALCULATE (

    COUNTROWS (PBIPND_vwPosition_Private ),

 

    KEEPFILTERS (

        PBIPND_vwPosition_Private[StartDate] >= __startDate &&

        PBIPND_vwPosition_Private[EndDate] <= __endDate &&

        NOT ISBLANK ( PBIPND_vwPosition_Private[EndDate] )

    )

 

)