Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
I'm creating a report that requires each scrum team be placed in a column listing the persons in a roles row. I need to keep the rows aligned by role. The numbers of roles on each team may vary. So I need to create a table that I can do a right join with to ensure missing rows are replaced by blanks. I know how to do the report I just need help with the joining table.
I have this Data Table:
team_id role_id
Scrum_Team_1 | Developer |
Scrum_Team_1 | Developer |
Scrum_Team_1 | Tester |
Scrum_Team_2 | Developer |
Scrum_Team_2 | Program_Manager |
Scrum_Team_3 | Developer |
Scrum_Team_3 | Developer |
Scrum_Team_3 | Developer |
Scrum_Team_3 | Tester |
Scrum_Team_3 | Program_Manager |
Scrum_Team_3 | Program_Manager |
and the needed table would be this:
team_role_count
Scrum_Team_1Developer1 |
Scrum_Team_1Developer2 |
Scrum_Team_1Developer3 |
Scrum_Team_1Tester1 |
Scrum_Team_1Program_Manager1 |
Scrum_Team_1Program_Manager2 |
Scrum_Team_2Developer1 |
Scrum_Team_2Developer2 |
Scrum_Team_2Developer3 |
Scrum_Team_2Tester1 |
Scrum_Team_2Program_Manager1 |
Scrum_Team_2Program_Manager2 |
Scrum_Team_3Developer1 |
Scrum_Team_3Developer2 |
Scrum_Team_3Developer3 |
Scrum_Team_3Tester1 |
Scrum_Team_3Program_Manager1 |
Scrum_Team_3Program_Manager2 |
The Report would be
Roles | Scrum Team 1 | Scrum Team 2 | Scrum Team 3 |
Developer | John | Peter | Sulley |
Developer | Sally | Ron | |
Developer | Joe | ||
Tester | Brian | Harry | |
Program_Manager | Larry | Willy | |
Program_Manager | Mathew |
Thanks for the help!
Solved! Go to Solution.
Hi @Anonymous ,
You need to add an index column with Query Editor firstly.
Then create a column “Value”:
Value = RANKX ( FILTER ( 'Table', EARLIER ( 'Table'[role_id] ) = 'Table'[role_id] && EARLIER ( 'Table'[team_id] ) = 'Table'[team_id] ), 'Table'[role_id], , )
Create an accumulate column “Count”:
Count = CALCULATE ( SUM ( 'Table'[Value] ), FILTER ( ALLEXCEPT ( 'Table', 'Table'[role_id] ), 'Table'[role_id] = EARLIER ( 'Table'[role_id] ) && 'Table'[team_id] = EARLIER ( 'Table'[team_id] ) && 'Table'[index] <= EARLIER ( 'Table'[index] ) ) )
Combine role_id and Count:
RoleColumn = IF ( NOT ( ISBLANK ( 'Table'[Count] ) ), 'Table'[role_id] & 'Table'[Count], BLANK () )
Create a new table by team_id and RoleColumn.
TempTable = VAR temp1 = DISTINCT ( 'Table'[team_id] ) VAR temp2 = CALCULATETABLE ( VALUES ( 'Table'[RoleColumn] ), FILTER ( 'Table', 'Table'[RoleColumn] <> BLANK () ) ) RETURN CROSSJOIN ( temp1, temp2 )
Now, you can get team_role_count by combining team_id with RoleColumn:
team_role_count = TempTable[team_id] & TempTable[RoleColumn]
Here is the final table.
Best Regards,
Eads
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Anonymous ,
You need to add an index column with Query Editor firstly.
Then create a column “Value”:
Value = RANKX ( FILTER ( 'Table', EARLIER ( 'Table'[role_id] ) = 'Table'[role_id] && EARLIER ( 'Table'[team_id] ) = 'Table'[team_id] ), 'Table'[role_id], , )
Create an accumulate column “Count”:
Count = CALCULATE ( SUM ( 'Table'[Value] ), FILTER ( ALLEXCEPT ( 'Table', 'Table'[role_id] ), 'Table'[role_id] = EARLIER ( 'Table'[role_id] ) && 'Table'[team_id] = EARLIER ( 'Table'[team_id] ) && 'Table'[index] <= EARLIER ( 'Table'[index] ) ) )
Combine role_id and Count:
RoleColumn = IF ( NOT ( ISBLANK ( 'Table'[Count] ) ), 'Table'[role_id] & 'Table'[Count], BLANK () )
Create a new table by team_id and RoleColumn.
TempTable = VAR temp1 = DISTINCT ( 'Table'[team_id] ) VAR temp2 = CALCULATETABLE ( VALUES ( 'Table'[RoleColumn] ), FILTER ( 'Table', 'Table'[RoleColumn] <> BLANK () ) ) RETURN CROSSJOIN ( temp1, temp2 )
Now, you can get team_role_count by combining team_id with RoleColumn:
team_role_count = TempTable[team_id] & TempTable[RoleColumn]
Here is the final table.
Best Regards,
Eads
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
User | Count |
---|---|
87 | |
78 | |
62 | |
61 | |
59 |
User | Count |
---|---|
160 | |
114 | |
100 | |
74 | |
65 |