Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Shape the future of the Fabric Community! Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions. Take survey.

Reply
lopezbec
Helper I
Helper I

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 Approval Ids.  I have used the lookup and it works when there is only one user, but it does not work when there are multiple IDs to look up.

 

lopezbec_0-1644421708422.png

lopezbec_1-1644421754079.png

Approval Sent To = LOOKUPVALUE('User Information List'[Title],'User Information List'[Id],'Process Review Status'[ApprovalSentToId])
 
Here is what is in my report, the one that is empty should have two names.  It appears that the lookup may not work, but I cannot find another option.  Any help would be appreciated.
lopezbec_2-1644422083465.png

 

1 ACCEPTED SOLUTION

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.

lopezbec_0-1644519056178.png

 

 

View solution in original post

10 REPLIES 10
goncalogeraldes
Super User
Super User

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

@goncalogeraldes 

 

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

lopezbec_0-1644434128022.png

 

 

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

@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

@goncalogeraldes 

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

lopezbec_0-1644500331698.png

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

lopezbec_4-1644500666620.png

lopezbec_9-1644501293995.png

 

lopezbec_3-1644500607198.png

lopezbec_10-1644501356120.png

 

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.

lopezbec_5-1644500780104.png

Here is what the data looks like:

ApprovalSentToID:

lopezbec_6-1644500937684.png

User table:

lopezbec_7-1644501059364.pnglopezbec_8-1644501125837.png

 

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

lopezbec_11-1644503023320.png

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.

 

 

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 )
)

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.

lopezbec_0-1644519056178.png

 

 

ValtteriN
Super User
Super User

Hi,

Is there a reason you can't have and active relationship between the tables (if so you can use passive + USERELATIONSHIP)?

ValtteriN_0-1644423239791.png



ValtteriN_1-1644423261475.png

ValtteriN_2-1644423282008.png


End result:

ValtteriN_3-1644423309661.png


Ping me with @ if you have questions

I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!

My LinkedIn: https://www.linkedin.com/in/n%C3%A4ttiahov-00001/







Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




@ValtteriN 

 

I am new to PowerBI.  I use the user table for both the ApproverSentToId as well as the FinalApproverID to get the names for the report.  For the ApproverSentToID there could be 1 to many.  The FinalApproverID has only one user.  I am not sure what you mean by using the "passive + USERELATIONSHIP)".  

Hi,

I meant that if you can't have an active relationship (like the one in my example) between the tables. You can create an inactive relationship and use USERELATIONSHIP function within CALCULATE to activate the relationship and combined this with variables to get the values you desire. However, I would recommend first trying to create an active relationship.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




@ValtteriN 

 

I have created an active relationship, but I do not understand how what you have above works, my ApprovalSentToID column will have 1 to many ids separated by commas.  I do not know if there will be one or 10 approver IDs in that field.  Can you provide the measure text of what you used?

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.