Find all children based on selected parent slicer value?

[sag_tyd]

Hello All,

New to PowerBI, please bare with me. 

I have monthly usage data from a SaaS software solution. You can read more about my data model in my previous post, but basically we get a usage report each month from a cloud deployment. All the monthly usage reports are pushed to the same table, and we have a separate table for a list of customers. 

The cloud deployment has three tiers of tenant: management, parent, and child. The management tenant is used to operate the platform and customers cannot access it. Customers are given a "parent" tenant, and they can create as many "child" tenants as they want. The data looks something like this:

Table 1: Usage Data

Month	Tenant Name	Parent Tenant	Path	Usage Metric(s)
January	management	-	management	...
January	parent1	management	management|parent1
January	childA	parent1	management|parent1|childA
January	childB	parent1	management|parent1|childB
January	parent2	management	management|parent2
January	childC	parent2	management|parent2|childC
February	management	-	management
February	parent1	management	management|parent1
February	childA	parent1	management|parent1|childA
February	childB	parent1	management|parent1|childB
February	parent2	management	management|parent2
February	childC	parent2	management|parent2|childC
...

Table 2: Customer Mapping

Customer Name	Tenant Name
Customer1	parent1
Customer2	parent2

I want to build a report using a slicer on the "Customer Name" - so you select a customer name and it shows you metrics from not only the parent tenant, but also any child tenants. The only way I've been able to do this is add all the child tenants to Table 2. The problem is our customers can create their own child tenants each month, so that is not scalable. Ideally, selecting a customer name would be able to find all child tenants and display their data alongside the parent tenant data. 

I've tried the solutions mentioned below:

1. https://community.powerbi.com/t5/Desktop/How-do-I-get-the-value-from-selected-slicer-and-then-filter... 
   This one looked promising, but still visualizations only contain the path up until tier 2. It does not drill down to tier 3. My code: 

   Path Contains Tenant = SEARCH(SELECTEDVALUE('Table2[Tenant Name]),SELECTEDVALUE(Table1[Path]),1,0)​

   
   
   
2. https://community.powerbi.com/t5/Desktop/Select-all-children-from-Parent-based-on-1-child-selected/m...
   This one searches for all children under a parent based on a child. I'm looking to search for all children under a parent based on the parent. 

Thank you for any help you can provide! I apologize if this is easy - still learning how to best model my data to generate value. Much appreciated!

[v-cgao-msft]

Hi @sag_tyd ,

Please try:

Path Contains Tenant =
VAR _SLICER =
    SELECTEDVALUE ( 'Table2'[Customer Name] )
VAR _TENANT =
    CALCULATETABLE (
        VALUES ( 'Table1'[Parent Tenant] ),
        FILTER ( ALL ( 'Table1' ), 'Table1'[Parent Tenant] = _SLICER )
    )
VAR _RESULT =
    IF (
        MAX ( 'Table1'[Tenant Name] )
            IN _TENANT
                && MAX ( 'Table1'[Parent Tenant] ) = _SLICER,
        1
    )
RETURN
    _RESULT

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly -- How to provide sample data

[sag_tyd]

Hello @v-cgao-msft,

I really appreciate you helping me!

Please help me check my understanding. In the file you attached, you did not have a data connection between the two tables:

Whereas in my model, I drew a connection between "Tenant Name" in both tables. Why should I not have a connection between them? 

The code you provided is partially working. I think that it is not working entirely because some of the customers have more than one parent tenant. So, for example, customer1 may have parent1a and parent1b:

Customer Name	Tenant Name
Customer1	parent1a
Customer1	parent1b
Customer2	parent2

It works great for the cases where a customer only has a single parent tenant. It is only when the customer has multiple parent tenants that it is not functioning. I apologies, I did not mention this in my original post as I did not know it would impact things and feared it would make my explanation more complicated. Any continued help is greatly appreciated.