Filter creation where you have multiple childs from multiple parent



I have these data:

Parent - company

L1- departments:


1.1. - team F1

1.2 - team F2

2. Accounting

2.1 - team A1

2.2 - team A2


3.1 - team HR1

3.2 - team HR2

Sometimes, there are situations when Team A1 from Accounting does work for Finance department.

I want to create a filter where, when I go to select 1.Finance department I would like to see all its teams (team F1 and team F2) + the other team from Accounting department (team A1).

In the same time, for the second filter, when I go to select a team (team A1) want to see all departments (Finance + Accounting) where is linked with.

Hope it makes sense what I would like to have.

Do you have any idea how can I do that? There is a mapping that I have to built?




  • Hi @fpopa

    The problem statement you have is a case of changing hierarchies. Based on the statements, I am assuming that teams can work in different departments at different points in time. Even without the time dimension, the hierarchies can change - which means it's better to store departments as a property of teams rather than linked hierarchically. You would still be able to aggregate as necessary using these properties.

    The two lists - departments and teams should not be linked through a hierarchy. Instead, store the relationship in a mapping module.

    Then you can use the previous module to derive the relationship and use the relationship as a filter. Formula: ITEM('L1 Department') = Team Properties.Primary Dept OR ITEM('L1 Department') = Team Properties.Secondary Dept

    Let me know if this helps!