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

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.

 

 

  • goncalogeraldes's avatar
    goncalogeraldes
    Super User
    4 years ago

    Could you try this then?

    Approval Sent To =
IF (
    IFERROR ( SEARCH ( ",", [ApprovalSentToId] ), -1 ) >= 0,
    LEFT ( RELATED ( 'User Information List'[Title] ), 3 ) & ", "
        & RIGHT ( RELATED ( 'User Information List'[Title] ), 3 ),
    LEFT ( RELATED ( 'User Information List'[Title] ), 3 )
)
    • lopezbec's avatar
      lopezbec
      Advocate I
      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.