Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to Solution.
Hi @jyoung_auto61 ,
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.
@jyoung_auto61 , try earlier
Power BI DAX- Earlier, I should have known Earlier: https://youtu.be/CVW6YwvHHi8
https://www.youtube.com/watch?v=cN8AO3_vmlY&t=17820s
maxx(filter('Table B', 'Table B [Role Name]= 'Table A' [Role]), 'Table B'[Group] )
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.
Hi @jyoung_auto61 ,
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
42 | |
21 | |
21 | |
14 | |
14 |
User | Count |
---|---|
43 | |
39 | |
33 | |
18 | |
17 |