azbasketcat
New Member
- Joined
- Feb 20, 2010
- Messages
- 30
I am trying to use a disconnected slicer to find the hourly rate of an employee that is less than or equal to the max date selected in the slicer.
I can get one formula to work, but when I try and shorten it, by replacing the second Calculate with a measure, I get the incorrect value. Are there certain times that you cannot use measures inside a Filter Expression? It seems like sometimes it works and other times it doesn't.
I can get the following to work:
[Applicable Hourly Rate] =
CALCULATE (
[Max Hourly Rate],
FILTER (
EMPLOYEE_SALARIES,
EMPLOYEE_SALARIES[EFFDT] =
CALCULATE (
MAX ( EMPLOYEE_SALARIES[EFFDT] ),
FILTER ( EMPLOYEE_SALARIES, EMPLOYEE_SALARIES[EFFDT] <= [Max Selected Date] )
)
)
)
But, I cannot get the following to work:
[Applicable Hourly Rate] = CALCULATE (
[Max Hourly Rate],
FILTER (
EMPLOYEE_SALARIES,
EMPLOYEE_SALARIES[EFFDT] = [Max Effective Date in Employee_Salaries]
)
)
where
[Max Effective Date in Employee_Salaries] =
CALCULATE (
MAX ( EMPLOYEE_SALARIES[EFFDT] ),
FILTER ( EMPLOYEE_SALARIES, EMPLOYEE_SALARIES[EFFDT] <= [Max Selected Date] )
)
FYI –
[Max Hourly Rate] = MAX(EMPLOYEE_SALARIES[HOURLY_RT])
[Max Selected Date] = MAX(Date[DT])
Thanks for any thoughts. Randy
I can get one formula to work, but when I try and shorten it, by replacing the second Calculate with a measure, I get the incorrect value. Are there certain times that you cannot use measures inside a Filter Expression? It seems like sometimes it works and other times it doesn't.
I can get the following to work:
[Applicable Hourly Rate] =
CALCULATE (
[Max Hourly Rate],
FILTER (
EMPLOYEE_SALARIES,
EMPLOYEE_SALARIES[EFFDT] =
CALCULATE (
MAX ( EMPLOYEE_SALARIES[EFFDT] ),
FILTER ( EMPLOYEE_SALARIES, EMPLOYEE_SALARIES[EFFDT] <= [Max Selected Date] )
)
)
)
But, I cannot get the following to work:
[Applicable Hourly Rate] = CALCULATE (
[Max Hourly Rate],
FILTER (
EMPLOYEE_SALARIES,
EMPLOYEE_SALARIES[EFFDT] = [Max Effective Date in Employee_Salaries]
)
)
where
[Max Effective Date in Employee_Salaries] =
CALCULATE (
MAX ( EMPLOYEE_SALARIES[EFFDT] ),
FILTER ( EMPLOYEE_SALARIES, EMPLOYEE_SALARIES[EFFDT] <= [Max Selected Date] )
)
FYI –
[Max Hourly Rate] = MAX(EMPLOYEE_SALARIES[HOURLY_RT])
[Max Selected Date] = MAX(Date[DT])
Thanks for any thoughts. Randy