Hierarchize filter drop-down control based on URL parameter

Q&A
, , ,
1

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

2

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

3

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.

4

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:

image
image836×568 22.4 KB

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

image
image833×564 13.2 KB

and moved the control to the sheet.

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

image
image433×698 15.2 KB

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

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

image
image380×589 14 KB

and with team Level = 2:

image
image384×682 15.9 KB

Let me know if this approach works in your scenario.

Many Thanks,
Andrew

5

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

6

Hello @albmargar !

Are you running into this problem still or were you able to find a workaround?

From my understanding of the issue, you should be able to able to use @abacon’s same logic to get the TeamMember names into a control filter rather than a table.

7

Hi @WLS-D, I almost forgot about this question since I haven’t implemented it yet. However, after taking a second look to @abacon answer, and as you say, I think it can also be applied to filters.

I’ll mark it as the solution and in the future I’ll test it.

Thanks for your time!