Results 1 to 2 of 2

Thread: Lookupvalue with multiple records
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Jul 2015
    Posts
    35
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Lookupvalue with multiple records

    Hi all

    I need some help on PowerBI

    Im trying to lookup a value from another table, except that other table has multiple rows with the same value that im looking up.

    This ofcourse results in an error.

    Each row has a 'createdon' date, so I thought it would be best to lookup the row with the earliest 'createdon' date.

    So in short, i need a lookupvalue [or a summarize formula] which will lookup the 'customerid_account.name' column within the 'opportunity' table, and return the most recent 'statuscode' (also from within the opportunity table)

    I hope this is clear. Ive tried various options but I cant seem to get it.

    Thanks in advance
    Ali

  2. #2
    Board Regular
    Join Date
    Jul 2014
    Location
    The Netherlands
    Posts
    1,263
    Post Thanks / Like
    Mentioned
    11 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Lookupvalue with multiple records

    Hi Try this:

    try a calculated column on Opportunity Table with this:
    =
    VAR Mydate =
    CALCULATE (MIN ( AnotherTable[Date] ), FILTER ( CALCULATETABLE ( VALUES ( AnotherTable ) ), AnotherTable[Date] = Opportunity[CreatedOnDate]))
    RETURN
    CALCULATE (MIN ( AnotherTable[Value] ), FILTER ( CALCULATETABLE ( VALUES ( AnotherTable) ), AnotherTable[Date]= mydate ))
    Last edited by jorismoerings; Aug 19th, 2019 at 01:45 AM.
    --------------------------------------------------------------------------------
    If you found my answer helpful, please hit the Like or Thank button.

    Please follow the forum Rules and Guidelines and use Code tags around your VBA code.

    Remember: test VBA code always on a copy of your file because usually you can't use <undo>

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •