Lookup value based on isfiltered

[Jaime_FOS]

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(ABSENCE_DATA[Employee Status],ABSENCE_DATA[EmployeeID],'CrossJoin Table 1'[EmployeeID],ABSENCE_DATA[startdate].[Date],'CrossJoin Table 1'[CAL_DATE],"Working").

What I'm trying to do is create a measure whereby if a date is selcted via a slicer, then return the status for that particular date. If no date is selected, then default to today's status. I've been using isfiltered for my calculated measures, as I can apply a date filter in the dax. However, I'm not able to do this with a lookupvalue. Is there a workaround, or is there another way that I can get the same result as the lookupvalue, and apply a filter within the measure?

Thanks in advance!

[v-lionel-msft]

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.

[az38 · Accepted solution]

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

[Jaime_FOS]

This worked perfect! Thanks 🙂