PATH Function/Top Down Reporting Assistance

[TakoTako]

Hi Team,

I found a great method in this forum to build out reporting lines using PATH. However, I have 2 challenges with this methodology and I couldn't find a solution anywhere so I figured I'd start here. I am building manager reporting lines starting at the CEO.

However, I do not want employees that are NOT managers showing up in the dropdown list, if possible. The other requirement is that I do not want it to include the manager's record if I am filtering on the manager to get records for his/her team.

The specific questions are:

1. How can I make the layers (Ex Layer 1, 2, 3, 4) to only show the manager's name? I don't want employees that are not managers in the list (Ex. Fred Flintstone because he isn't a manager).
2. Similarly, if I choose Barney Rubble as layer 3, it should only return Fred and Dino. Barney should no be included because he isn't managing himself.

I have included a link to a sample PBIX file I have in my Dropbox. Below is an image:

New Manager =

VAR CurrentEmployee = 'Active'[Manager Number]

RETURN

IF (

COUNTROWS (

FILTER ( Active, 'Active'[Employee Number] = CurrentEmployee )

)

= 0,

'Active'[Employee Number],

'Active'[Manager Number]

)

---

Below builds the reporting line path:

Top Hierarchy =

PATH (

Active[Employee Number],

Active[New Manager]

)

---

Any help would be great! Thanks so much!

Best,
A

[v-zhangti · Accepted solution]

Hi, @TakoTako 

For the first problem you can try the following methods.

Measure:

Measure =
IF (
    SELECTEDVALUE ( Active[Name] )
        <> CALCULATE (
            MAX ( Active[Manager] ),
            FILTER ( ALL ( Active ), [Manager] = SELECTEDVALUE ( Active[Name] ) )
        ),
    0,
    1
)

Then put the measure into the filter of layer 1, 2, 3 and 4 slicer and set it equal to 1.

This way no non-manager names will appear in the layers.

The 2nd problem can be tried as follows.

Measure 2 = 
IF(SELECTEDVALUE(Active[Layer 3])=SELECTEDVALUE(Active[Name]),0,1)

Put it in the filter as well and set it equal to 1. The output at this point is shown in the figure.

Is this the result you expect?

Best Regards,

Community Support Team _Charlotte

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

[TakoTako]

Hi Charlotte,

Thank you so much for this! The first measure works beautifully and I have added it to the model! This removes so much noise from the layers. 

The 2nd one is great, too, however, it appears that I could really only do that to one layer AND I would have to apply it to each visual in my workbook. If I wanted to do it for each layer to ensure that person's record doesn't populate, it truncates too many rows. Is there any way to make this a little more forgiving? 😁 If not, item 1 is more than sufficient for right now. 

Thank you!

[v-zhangti]

Hi, @TakoTako 

The second measure is to add to the view when you need to filter which view, which is by far the easiest way I can think of.

Best Regards,

Community Support Team _Charlotte

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

[TakoTako]

Thanks Charlotte. Much appreciated.

[TakoTako]

Expanded for more clarity.