Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.
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.
Solved! Go to 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], ", ")
@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?
@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], ", ")
User | Count |
---|---|
20 | |
14 | |
11 | |
8 | |
6 |
User | Count |
---|---|
23 | |
23 | |
20 | |
15 | |
10 |