Forum Discussion

Jaime_FOS's avatar
Jaime_FOS
Regular Visitor
6 years ago
Solved

Lookup value based on isfiltered

Hi,   I have a table listing in my report that shows a person's name and their status e.g. 'working', homeworking' or 'out of office'. I'm using a lookupvalue to return this status - LOOKUPVALUE(AB...
Need Help
  • az38's avatar
    az38
    6 years ago

    Hi Jaime_FOS 

    try a measure like

    Measure = 
var SelectedDate = IF(ISFILTERED('CrossJoin Table 1'[CAL_DATE]), SELECTEDVALUE('CrossJoin Table 1'[CAL_DATE]), TODAY())
var CurrentEmployee = MAX('CrossJoin Table 1'[EmployeeID])
var Result = CALCULATE(MAX(ABSENCE_DATA[Employee Status]), ABSENCE_DATA[startdate].[Date] = SelectedDate, ABSENCE_DATA[EmployeeID] = CurrentEmployee )
RETURN
COALESCE(Result,"Working")
v-lionel-msft's avatar
v-lionel-msft
Community Support
6 years ago

Hi Jaime_FOS ,

 

You may need to do like this.

1. Create a calculated column in 'CrossJoin Table'.

 

Column = 
LOOKUPVALUE(
    ABSENCE_DATA[Employee Status],
    ABSENCE_DATA[EmployeeID], 'CrossJoin Table'[EmployeeID],
    ABSENCE_DATA[startdate], 'CrossJoin Table'[CAL_DATE],
    ABSENCE_DATA[Employee Status], "Working"
)

 

2. Create a measure.

 

Measure = 
IF(
    ISFILTERED('CrossJoin Table'[CAL_DATE]),
    SELECTEDVALUE('CrossJoin Table'[Column]),
    CALCULATE(
        MAX('CrossJoin Table'[Column]),
        FILTER( ALL('CrossJoin Table'), 'CrossJoin Table'[CAL_DATE] = TODAY() )
    )
)

 

 

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.