Filter creation where you have multiple childs from multiple parent
I have these data:
Parent - company
1.1. - team F1
1.2 - team F2
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?
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!