Forum Discussion

evebarratt's avatar
evebarratt
Helper I
4 years ago
Solved

LOOKUPVALUE within a range

Hi, I have a trouble getting data using LOOKUPVALUE dax.   I used below to get 'Created By'. But the issue is that the 'Date' is not always exactly same. Sometimes it is 1~2 seconds different then ...
Need Help
  • smpa01's avatar
    smpa01
    4 years ago

    evebarratt  you can write a measure like this

    Measure =
VAR _date =
    MAX ( 'Table 2'[Date] )
VAR _date2 =
    _date - TIME ( 0, 0, 2 )
VAR _date3 =
    _date + TIME ( 0, 0, 2 )
VAR _date4 =
    _date - TIME ( 0, 0, 1 )
VAR _date5 =
    _date + TIME ( 0, 0, 1 )
VAR _return1 =
    CALCULATE (
        MAX ( 'Table 1'[Created By] ),
        TREATAS (
            CROSSJOIN ( VALUES ( 'Table 2'[ID] ), { _date } ),
            'Table 1'[ID],
            'Table 1'[Date]
        )
    )
VAR _return2 =
    CALCULATE (
        MAX ( 'Table 1'[Created By] ),
        TREATAS (
            CROSSJOIN ( VALUES ( 'Table 2'[ID] ), { _date2 } ),
            'Table 1'[ID],
            'Table 1'[Date]
        )
    )
VAR _return3 =
    CALCULATE (
        MAX ( 'Table 1'[Created By] ),
        TREATAS (
            CROSSJOIN ( VALUES ( 'Table 2'[ID] ), { _date3 } ),
            'Table 1'[ID],
            'Table 1'[Date]
        )
    )
VAR _return4 =
    CALCULATE (
        MAX ( 'Table 1'[Created By] ),
        TREATAS (
            CROSSJOIN ( VALUES ( 'Table 2'[ID] ), { _date4 } ),
            'Table 1'[ID],
            'Table 1'[Date]
        )
    )
VAR _return5 =
    CALCULATE (
        MAX ( 'Table 1'[Created By] ),
        TREATAS (
            CROSSJOIN ( VALUES ( 'Table 2'[ID] ), { _date5 } ),
            'Table 1'[ID],
            'Table 1'[Date]
        )
    )
VAR _x =
    MAX ( MAX ( MAX ( MAX ( _return1, _return2 ), _return3 ), _return4 ), _return5 )
RETURN
    _x

     

     

parry2k's avatar
parry2k
Super User
4 years ago

evebarratt it is pretty straight forward if that is the case, use the following expression to add a column, and I set 2 seconds as +/-, you can change the value from 2 seconds to whatever you think is best for you:

 

Required = 
VAR __addseconds = 2
VAR __time = TIME ( 0, 0, __addseconds )
VAR __actualDate = Table2[Date]
VAR __startDate = __actualDate - __time
VAR __endDate = __actualDate + __time

RETURN 
CALCULATE ( MAX ( Table1[Created By] ), Table1[Date] >= __startDate, Table1[Date] <= __endDate )

 

✨ Follow us on LinkedIn

 

Learn about conditional formatting at Microsoft Reactor

My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

 

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

  • evebarratt's avatar
    evebarratt
    Helper I
    4 years ago

    parry2k I couldn't get the result I wanted with the dax provided so had to choose the other one. Thanks a lot!