Forum Discussion
Issues with data hierachy/multiple columns in matrix visualization
Hello all,
I am experiencing the following problem:
Each month I check, whether all teams within my organization have completed a specific task. I have constructed a report that allows me to keep track of that with the help of a table visualization (see below).
As I have more than 200 teams to take care of, I decided that I no longer want to contact the teamlead only, but their department head also (=> a higher level) while still being able to track all the team's status on whether they completed the task or not. However, this is where I run into problems.
I changed the visualization from a table to a matrix to implement the department->team hierarchy. If I only look at the completion status, this solution seems to work quite well:
As I want to contact the respective department heads and team leads, I want to add their names to the matrix.
I use the following raw data (sample due to data protection):
| TEAM_ID | TEAM_SHORTNAME | PARENT_TEAM_ID | PARENT_TEAM | TEAM_LONGNAME | TEAMLEAD | TASK_COMPLETION | HAS_TO_DO_TASK |
| 1 | ADWS1 | Software Department | Angela Merkel | False | |||
| 11 | ADWS11 | 1 | ADWS1 | Software Team 1 | Max Mustermann | 100,00 % | True |
| 12 | ADWS12 | 1 | ADWS1 | Software Team 2 | Paul McCartney | 100,00 % | True |
| 13 | ADWS13 | 1 | ADWS1 | Software Team 3 | Shawn Carter | 100,00 % | True |
| 14 | ADWS14 | 1 | ADWS1 | Software Team 4 | John Lennon | 0,00 % | True |
As you can see, the department head is "Angela Merkel". When I add TEAMLEAD to the value section of the matrix visualization however, "John Lennon" is shown as the department head. Of course this happens because the data is summarized in that row and not sourced from the raw data.
So I thought about creating a calculated column where I use CONCATENATE to have a single expression for team/department identifier and team lead/department head (e.g. "ADWS1 (Angela Merkel)" or "ADWS11 (Max Mustermann)") and use that as my hierachy/row. But surely there must be a more elegant way to do this and to display the identifier and the team lead in separate columns?
Any help is much appreciated!
Thanks a lot in advance and kind regards
Marcel
Hi Anonymous ,
Please recreate a new measure.
Measure 2 = var aa = IF(SELECTEDVALUE('Table'[PARENT_TEAM])<>BLANK(),SELECTEDVALUE('Table'[PARENT_TEAM]),SELECTEDVALUE('Table'[TEAM_SHORTNAME])) return if(ISINSCOPE('Table'[TEAM_SHORTNAME]),[Measure],CALCULATE(MAX('Table'[TEAMLEAD]),FILTER(ALL('Table'),'Table'[PARENT_TEAM]=BLANK()&&'Table'[TEAM_SHORTNAME]=aa)))Then filter the PARENT_TEAM.
Best Regards
Community Support Team _ Polly
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
7 Replies
Hi Anonymous ,
Where do you want to show the department head? And which column is the the department head? Please provide your desired output.
Best Regards
Community Support Team _ Polly
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.