Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Can a Lookupvalue value itself?

I have a list of user roles in the Role column in Table A, unfortunately that column has a mix of the names of the Role Groups and the different roles in the groups. i.e that column contains Accounting (role group), AP Clerk, AR Clerk (individual roles). I need a column in this table that is all Role Groups.

 

In a seperate table Table B - I have the Group, Role and abbreviation separted out, like this.

 

I started with adding a new column like this.

new column = LOOKUPVALUE('Table B [Group], 'Table B [Role Name], 'Table A' [Role] ???

However I need the value in the new column to remain the Table A [Role] value if it doesn't match the criteria in the lookupvalue. 

How do I do that? 

FYI, learning DAX. Thank you in advance.

 

 

DAX
LOOKUPVALUE
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi Anonymous ,

     

    I suggest you to try code as below to create a calculated column in Table A.

    Group =
VAR _GROUPLIST =
    VALUES ( 'Table B'[Group] )
VAR _GROUP =
    CALCULATE (
        MAX ( 'Table B'[Group] ),
        FILTER ( 'Table B', 'Table B'[Role Name] = EARLIER ( 'Table A'[Role] ) )
    )
RETURN
    IF ( 'Table A'[Role] IN _GROUPLIST, 'Table A'[Role], _GROUP )

     

    Best Regards,
    Rico Zhou

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

3 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Thanks for the suggestion but that gave me the same result as the lookupvalue I tried. The cells that already have the group name come back blank. I need the cells with the group name to come back with the same group name that they have.

      • Anonymous's avatar
        Anonymous
        Not applicable
        3 years ago

        Hi Anonymous ,

         

        I suggest you to try code as below to create a calculated column in Table A.

        Group =
VAR _GROUPLIST =
    VALUES ( 'Table B'[Group] )
VAR _GROUP =
    CALCULATE (
        MAX ( 'Table B'[Group] ),
        FILTER ( 'Table B', 'Table B'[Role Name] = EARLIER ( 'Table A'[Role] ) )
    )
RETURN
    IF ( 'Table A'[Role] IN _GROUPLIST, 'Table A'[Role], _GROUP )

         

        Best Regards,
        Rico Zhou

         

        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.