Reply
Highlighted
Frequent Visitor
Posts: 11
Registered: ‎06-28-2018

Slicers & Measures with Parent/Child Hierarchies

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 1Customer 1
Project 1.1Customer 1
Project 1.2Customer 2
Project 1.1.1Customer 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 12
Customer 21
Customer 31
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 1Customer 1
Project 1.2Customer 2

 

 and the following counts:

 

Customer 11
Customer 21
Total2

 

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 1Customer 1
Project 1.1Customer 1
Project 1.1.1Customer 3

 

Customer 12
Customer 31
Total3

 

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

Community Support Team
Posts: 3,819
Registered: ‎07-09-2016

Re: Slicers & Measures with Parent/Child Hierarchies

@CaFerguson,

 

You may try using PATHCONTAINS.

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

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Frequent Visitor
Posts: 11
Registered: ‎06-28-2018

Re: Slicers & Measures with Parent/Child Hierarchies

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?