Forum Discussion

IntaBruce's avatar
IntaBruce
Icon for Resolver I rankResolver I
1 year ago
Solved

LOOKUPVALUE with multiple results

Hi,

I am using LOOKUPVALUE to find the staff member associated with a particular record.  However, the table containing the information has multiple records for different dates.  In other words, one staff member could be associated with the record on the 1st September but another on th 5th .

So currently my code returns an error:

New Column:    Assigned Staff = LOOKUPVALUE(AssignedStaff[Staff Name],AssignedStaff[Record_UID],[UID])
   A table of multiple values was supplied where a single value was expected.
The AssignedStaff table has a CreatedDate (always populated) and an UpdatedDate (populated only if the record has been amended). 
The assignment record that I want would be the most recent.
I've been scratching my head over this for a while and can't work out how to formulate the DAX expession.
Any guidance much appreciated.  Thank you
 
Need Help
  • IntaBruce's avatar
    IntaBruce
    1 year ago

    Sorry lbendlin, I have confused matters by misunderstanding the data myself.  After consulting with the developers who support the source database they have confirmed that the dates are a red herring as there will only ever be one record but that it has to match not just the related work order record but also met be of the correct assignment type and also be the Primary record.
    so, my final formula is:

    Assigned Staff = LOOKUPVALUE(AssignedStaff[Staff Name],AssignedStaff[Record_UID],[UID],AssignedStaff[StaffAssignmentType],1,AssignedStaff[IsPrimary],TRUE())
     
    Thank you for your time and efforts in helping me solve my problem.  Kudos given 🙂

6 Replies

  • lbendlin's avatar
    lbendlin
    Icon for Super User rankSuper User
    1 year ago
    I am using LOOKUPVALUE

     

    Maybe don't?  Consider using TREATAS instead, and then a TOPN(1) on the results.

    • IntaBruce's avatar
      IntaBruce
      Icon for Resolver I rankResolver I
      1 year ago

      That might be a little beyond me at the moment, I've not come across TREATAS before.  How might I use it in this scenario?

      • lbendlin's avatar
        lbendlin
        Icon for Super User rankSuper User
        1 year ago

        Here's the documentation:

        TREATAS function - DAX | Microsoft Learn

         

        Pseudo code:

         

        Assigned Staff = CONCATENATEX(TOPN(1,TREATAS({[UID]},AssignedStaff[Record_UID])),[Staff Name])

         

        You'll want to validate that - and maybe use FILTER instead, or the data model if the tables are related.

         

        If you can provide some sample data I can give a more solid answer.