Replace blank row header with text in matrix

[Anonymous]

What I'm trying to do: 

Replace the blank row header with "HR roll-up not available" and return proper counts.

Source data tables:

Incs:  a table of incident tickets which includes the Affected User.  If, when the ticket was created, the Affected User is unknown, the value of "Guest" is used.

        and

Talent:  a table with all the users with their management chain in the columns (Manager 1, Manager 2, Manager 3, etc.).

These tables are linked (many to one) on the Affected User.

Issue:

When I create a simple matrix that shows the total number of users under each manager and the number of incident tickets created for those users, I get the results displayed above (which has the correct counts).  If, however, the Affected User is "Guest", there is no management chain for them so they get counted and displayed with a blank row heading value (the yellow highlighting).  I've been asked to replace the blank row heading with the text "HR roll-up not available".  I've tried a number of methods to accomplish this and none have been successful.  I've been working at this for over 2 business days.

• When I created columns in Incs table using the formula below, the resulting matrix populated the field and properly counted the Inc Count but showed the total number of users in each row of the Talent Count vs the number of users reporting up to each manager.

_Inc Affected User Mgr 2 = IF(ISBLANK(RELATED('Talent Link Data'[Manager Level 2])),"HR roll-up not available",RELATED(('Talent Link Data'[Manager Level 2])))

• When I tried to do the same thing in the Talent table, it simply created a row with the HR roll-up text that counted the managers listed in matrix with their total count of tickets but still put the count associated with the Guests in a blank row.

_Talent Manager 2 TEST = IF('Talent Link Data'[Manager Level 2]="","HR roll-up not available",'Talent Link Data'[Manager Level 2])

• I've also tried adding a row to the source data with the user name of "Guest" and "HR roll-up not available" in each of the managers fields.  That failed also.

How do I get the blank row heading to show the desired text while still producing accurate counts?

Help, please?

[v-xiaotang]

Hi @Anonymous 

It seems that there is a blank in your raw data, try to replace it  with "HR roll-up not available", 

if the problem is still not solved, could you share some sample data so that we can reproduce and solve it .

Best Regards,

Community Support Team _Tang

If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

@v-xiaotang 

I can't really do that because the data is proprietary and I have no idea how to recreate the problem with bogus data.  ☹️

[AlexisOlson]

"I've also tried adding a row to the source data with the user name of "Guest" and "HR roll-up not available" in each of the managers fields.  That failed also."

This seems like a good idea. Can you explain how it failed specifically?

[Anonymous]

Hi Alexis,

I end up with the same structure I show in the 2 bullets above, depending on which fields I use.