Issues with data hierachy/multiple columns in matrix visualization

[marcel97]

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

[marcel97]

Can't thank you enough @Anonymous ! This is exaclty what I was looking for!

Thanks a lot and kind regards! 🙂

Marcel

[Anonymous]

Hi @marcel97 .

Please have a try,

Create a measure.

Measure = SELECTEDVALUE('Table'[TEAMLEAD])

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.

[marcel97]

Thanks a lot @Anonymous ! I could recreate this with ease in my file 🙂 

At least there is no more incorrect teamlead shown at department level. Is there a way however to show the department head there also? See below:

Appreciate any help! Thanks and kind regards

Marcel

[Anonymous]

Hi @marcel97 ,

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.

[marcel97]

Thanks for the quick response @Anonymous .

My raw data does not differentiate between Teamleads and Department Heads, they are both in Column TEAMLEAD. However, via my TEAM_ID and PARENT_TEAM_ID my data implies a hierarchy with parents being departments and children being teams.

ADWS1 for example is parent to all other teams (=> department of those teams) and has Angela Merkel as a teamlead (=>department head).

Ideally the department head should show up in the same column in my visualization as the teamleads of the child teams under it (as shown in the attachment to my last reply).

However, I'm open to alternative solutions if that's not possible.

Thanks!

Marcel

[Anonymous · Accepted solution]

Hi @marcel97 ,

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.

[marcel97]

Hey @Anonymous ! I'd have to ask another question regarding this topic. If I had more than one level in my hierarchy, how would I need to adjust the measure(s)? E.g. I have ADWS1 as my department, ADWS11 as a team an ADWS111 as a subteam. The PARENT_TEAM_ID to ADWS11 is not blank and I had issues figuring out how to adjust your measure.

Please find attached the raw data:

TEAM_ID	TEAM_SHORTNAME	PARENT_TEAM_ID	L1	L2	L3	TEAM_LONGNAME	TEAMLEAD	TASK_COMPLETION	HAS_TO_DO_TASK	PATH
1	ADWS1		ADWS1			Software Department	Angela Merkel		False	1
11	ADWS11	1	ADWS1	ADWS11		Software Team 1	Max Mustermann		False	1|11
111	ADWS111	11	ADWS1	ADWS11	ADWS111	Software Subteam 11	Maxime Musterfrau	1	True	1|11|111
112	ADWS112	11	ADWS1	ADWS11	ADWS112	Software Subteam 12	Barack Obama	1	True	1|11|112
12	ADWS12	1	ADWS1	ADWS12		Software Team 2	Paul McCartney	1	True	1|12
13	ADWS13	1	ADWS1	ADWS13		Software Team 3	Shawn Carter	1	True	1|13
14	ADWS14	1	ADWS1	ADWS14		Software Team 4	John Lennon	0	True	1|14

Again, I would need to have the Teamleads (Max Mustermann for ADWS11 and Angela Merkel for ADWS1) in the same column:

Any idea is greatly appreciated!

Cheers 

Marcel