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
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] )

    )

 

)