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(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!

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

3 Replies

  • az38's avatar
    az38
    Community Champion
    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.