Forum Discussion

lopezbec's avatar
lopezbec
Advocate I
4 years ago
Solved

DAX Help with Lookup for multiple users

I have a Sharepoint list that I am creating a PBI report for.  This sharepoint list will have one or more IDs for an approval workflow. I want to show who the Approval request went to using the Appr...
  • lopezbec's avatar
    lopezbec
    4 years ago

    Thanks for your assistance, but I found a different solution.  AssignedTo is also found under FieldValuesAsText.  I selected the fields I needed and it is already available.  No function needed.

     

     

goncalogeraldes's avatar
goncalogeraldes
Super User
4 years ago

Hello there lopezbec ! The search values need to ideally be scalar. You should split the column in power query to then do the LOOKUP, but you can try the following:

 

Approval Sent To =
IF (
    IFERROR ( SEARCH ( ",", [ApprovalSentToId] ), -1 ) >= 0,
    LEFT ( RELATED ( 'User Information List'[Title] ), 3 ),
    LEFT ( RELATED ( 'User Information List'[Title] ), 3 ) & ", "
        & RIGHT ( RELATED ( 'User Information List'[Title] ), 3 )
)

 

Hope this answer solves your problem!
If you need any additional help please @ me in your reply.
If my reply provided you with a solution, please consider marking it as a solution ✔️ or giving it a kudoe 👍
Thanks!

You can also check out my LinkedIn!

Best regards,
Gonçalo Geraldes

  • lopezbec's avatar
    lopezbec
    Advocate I
    4 years ago

    goncalogeraldes 

     

    This did not work, when there is only one approver name it splits the name with a comma.

     

     

    Can you give me more information on how to split the column?

    • goncalogeraldes's avatar
      goncalogeraldes
      Super User
      4 years ago

      lopezbec my bad, try this:

      Approval Sent To =
IF (
    IFERROR ( SEARCH ( ",", [ApprovalSentToId] ), -1 ) > 0,
    LEFT ( RELATED ( 'User Information List'[Title] ), 3 ),
    LEFT ( RELATED ( 'User Information List'[Title] ), 3 ) & ", "
        & RIGHT ( RELATED ( 'User Information List'[Title] ), 3 )
)

      Hope this answer solves your problem!
      If you need any additional help please @ me in your reply.
      If my reply provided you with a solution, please consider marking it as a solution ✔️ or giving it a kudoe 👍
      Thanks!

      You can also check out my LinkedIn!

      Best regards,
      Gonçalo Geraldes

      • lopezbec's avatar
        lopezbec
        Advocate I
        4 years ago

        goncalogeraldes 

        If I do not have a relationship between the tables, I get this error:

        I created a relationship with ApprovalSentToID as well as FinalApproverID to ID in the User Information List

         

         

        It then works, but I get the same results and it is not pulling in anything for both approvers.  The second row should have 2 approver names.

        Here is what the data looks like:

        ApprovalSentToID:

        User table:

         

        I changed the number from 3 to 15 and I get these results

        Now it shows the full name, but when there is a single approver, it lists it twice.  When there is multiple, still nothing.

         

        Another Comment, I don't think I can split the columns, because I will not know if there is one approver, or 10, the approver list is dynamic based upon another list for Department in SharePoint.