LOOKUPVALUE DAX with Many to Many relationship and multiple conditions

[ezFlow]

Hi,
I have 2 Tables, 
1. Roles 
2. Access

I want to create a colum in the Access table with the value of the 'Role_ID' field from the Roles table:
Where 'Dept Name' field in Roles = 'Department' field in Access, and RoleTitle in Roles = Title in the Access table.
There is a Many to Many relationship based on the Deptartmetns and Role Titles.

 

[WinterMist]

@ezFlow -

All "X" functions iterate over a table (Parameter 1), evaluating an expression (Parameter 2) for each row in that table.

MAXX just takes the max value of all the evaluated expressions over all the rows.

--THIS CODE GETS ONLY 1 VALUE
new column =
maxx( --MAXX ITERATES TABLE P1, PERFORMING EXPRESSION P2
filter( --P1: FILTER RETURNS A FILTERED SUBSET OF ROLES TABLE
Roles,
Roles [Department] = Access [Dept Name]
&& Roles [RoleTitle ] = Access [Roles ]
),
Roles[Role_ID] --P2: EVALUATES EXP ROLE_ID FOR EACH ROW IN P1
)

https://docs.microsoft.com/en-us/dax/maxx-function-dax

Hope this is helpful to you.

Nathan

[ezFlow]

Thank you Amit: That worked:

new column

maxx(filter(Roles , Roles [Department] = Access [Dept Name] && Roles [RoleTitle ] = Access [Roles ]) ,Roles[Role_ID])

Do you have any souce I can read on the MAXX command and how it works?

[amitchandak · Accepted solution]

@ezFlow , You can take one value or concatenate values

new column

maxx(filter(Roles , Roles [Department] = Access [Dept Name] && Roles [RoleTitle ] = Access [Roles ]) ,Roles[Role_ID])

or

concatenatex(filter(Roles , Roles [Department] = Access [Dept Name] && Roles [RoleTitle ] = Access [Roles ]) ,Roles[Role_ID], ", ")