Hierarchize filter drop-down control based on URL parameter

[albmargar]

This question is related to this other one, but adds another layer of complexity. Please see the related link since I have omitted information like passing the parameter via URL.

I am going to use the same example as @Nate:
TeamID TeamMember
1 Alice
1 Ben
1 Chris
2 Dave
2 Eve
2 Fran

Let’s imagine that TeamId=2 has visibility over TeamId=1, so the filters would look like:
TeamId=1 → Alice, Ben, Chris
TeamId=2 → Alice, Ben, Chris, Dave, Eve, Fran

How could I achieve this? I have replicated @Nate’s solution, but it only achieves the equality condition, not the less or equal condition. Any suggestions?

Thanks for your time!
Alberto

[mukilan]

Hi @albmargar

One way to achieve this might be to create a new dataset just to be used in the control options.

The dataset will look something like this

TeamID TeamMember
1 Alice
1 Ben
1 Chris
2 Dave
2 Eve
2 Fran
2 Alice
2 Ben
2 Chris

You basically will have to duplicate team 1 and add all of the members again to the dataset with team id = 2 since team 1 is being overseen by team 2. This is will need to be repeated for every team overseeing case via SQL. Then you can follow the same steps from the original post

[albmargar]

Thanks @mukilan! Possibly it is not the most efficient way to solve the problem, but definitively your answer solves it!

I will keep the thread open for more suggestions since I would prefer a solution without duplicating that much data. In my case I have the equivalent to 6 teams, with the lower team (the one who every other team has access) the biggest one.

[abacon]

Hi @albmargar,

The following is an alternative example I built that uses a parameter with a control and a filter for the TeamID measure:

Firstly, I created a parameter pHierarchyFilter as follows:

Then I created a control for the parameter called cHierarchyFilter as follows:

and moved the control to the sheet.

Then I added a table visual listing only the TeamMember names:

The final step is to add a filter for the table visual as follows:

This should result in a sheet that resembles the following, with team Level = 1:

and with team Level = 2:

Let me know if this approach works in your scenario.

Many Thanks,
Andrew

[albmargar]

Hi Andrew! Thanks a lot for replying and taking your time. However your answer does not solve the problem I am facing.

TeamMember names should be located in the control section as a filter or as a parameter so the user could interact with it and use it as a filter, not as a visual table.

If you can achieve the same result you provided as a filter instead of a visual table, that could solve the issue.

Cheers,
Alberto