Forum Discussion
LOOKUPVALUE DAX with Many to Many relationship and multiple conditions
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.
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], ", ")
3 Replies
- amitchandak
Super User
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], ", ")
- WinterMist
Impactful Individual
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
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?
