Slicers & Measures with Parent/Child Hierarchies

[Anonymous]

Hi, I've been using this link to build a report with a parent-child hierarchy, slicers, a matrix and a few other visuals.

https://www.daxpatterns.com/parent-child-hierarchies/

It's mostly been going well but there are a few things I've been having trouble with.

Take this as an example.

Project 1	Customer 1
Project 1.1	Customer 1
Project 1.2	Customer 2
Project 1.1.1	Customer 3

Project 1 is parent to Project 1.1 and 1.2, etc. (I do have a column for mapping Parent IDs).

If I were to slice and dice by customer I would still want to see children projects even if they were different customers.

If I chose Customer 1 I would still want to see all projects/sub-projects in a matrix because they are childern of a Project with customer 1. Currently, if I were to choose Customer 1 I would only see Project 1 and Project 1.1.

I also want to be able to measure a count of projects and sub-projects which I can display in visuals. For example:

Customer 1	2
Customer 2	1
Customer 3	1
Total	4

This would be the count I would get if I were to have no slicer selection or if I were to choose Customer 1.
Currently when I choose Customer 1 it only totals up to 2 for Project 1 and 1.1.

Is there a way of doing this?

Here's another case where Customer 2 is selected in a slicer.

We would see the following projects:

Project 1	Customer 1
Project 1.2	Customer 2

 and the following counts:

Customer 1	1
Customer 2	1
Total	2

In this example we would still want to see and count the parent even though they do not have the same customer as chosen, simply based on the fact the child has the chosen customer. Currently, the count only totals up to 1 for Project 1.2.

Lastly, when Customer 3 is chosen. We can ignore Project 1.2. Currently it does this but the count only counts Project 1.1.1.

Project 1	Customer 1
Project 1.1	Customer 1
Project 1.1.1	Customer 3

Customer 1	2
Customer 3	1
Total	3

This might be complex, but I was wandering if anyone was able to help, even with just a small part of my above requirements.

[v-chuncz-msft]

@Anonymous,

You may try using PATHCONTAINS.

https://community.powerbi.com/t5/Desktop/Summing-Aggregating-on-Parent-Child-Hierarchies/m-p/395005#M180194

[Anonymous]

Hi Sam,

Thanks for the response.

I had seen this post and tried that solution.

One of the issues I'm having is that the Count rolls up to the top level fine. But if I were to filter to Customer 2 as an example, it would count Projects 1 and 1.1 as Customer 2 in any visual.

This also doesn't help the situations where if I filter to Customer 1, in the matrix I can't see the child projects that come off that if they have a different customer. This is even more of a concern for me.

Any advice here?