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

 

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'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.

     

     

10 Replies

  • ValtteriN's avatar
    ValtteriN
    Community Champion
    4 years ago

    Hi,

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




    End result:


    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/



    • lopezbec's avatar
      lopezbec
      Advocate I
      4 years ago

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

      • ValtteriN's avatar
        ValtteriN
        Community Champion
        4 years ago

        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.

  • 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