# Filtering Arbitrary Hierarchies

**URL:** <https://community.amazonquick.com/t/filtering-arbitrary-hierarchies/29851>\
**Category:** Q&A\
**Tags:** filters, Business-Intelligence-Engineer, filters-controls-and-parameters, quick-sight\
**Created:** [May 14, 2024, 5:33pm UTC](https://community.amazonquick.com/t/filtering-arbitrary-hierarchies/29851 "2024-05-14T17:33:02Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![sirwin007](https://avatars.discourse-cdn.com/v4/letter/s/35a633/32.png) [@sirwin007](https://community.amazonquick.com/u/sirwin007)\
**Post date:** [May 14, 2024, 5:33pm UTC](https://community.amazonquick.com/t/filtering-arbitrary-hierarchies/29851/1 "2024-05-14T17:33:03Z")

</div>

My dashboard and data are multi-tenant.

Any given tenant defines their unique hierarchy, and the Reader is associated somewhere into that hierarchy where they can see from that node down. A given data row for the visual is at a specific point in the hierarchy and has that node’s GUID. Using row-level security I built a list of GUIDs that user can see.

My last issue is to be able to add a filter. Assume a tenant defined a 10 level hierarchy, and this Reader can see from level 7 down to 10. A user could select any Level in the filter values above 8 and see their permitted rows, or below 7 and see just a subset of their permitted rows. To populate the filter, I have another dataset with row-level security to provide the full tenant unique hierarchy.

I wanted to have a display value and then a bunch of spaces to hide a list of GUIDs that selection allows. However, the filter control inconveniently stripped out all those spaces. Now the visual shows “Level 8 guid1,guid2,…”. I need this list to write a filter that the data row’s GUID is IN the filter value (split by text/metadata).

Anyway to push that text off the screen? Or a better hack given hierarchy filters are not supported?

“Level 8 --guid,guid,guid,guid”

---

<div class="post-metadata">

**Author:** ![Xclipse](https://d2orw1595917va.cloudfront.net/user_avatar/community.amazonquick.com/xclipse/32/20455_2.png) [@Xclipse](https://community.amazonquick.com/u/Xclipse)\
**Post date:** [May 14, 2024, 8:23pm UTC](https://community.amazonquick.com/t/filtering-arbitrary-hierarchies/29851/2 "2024-05-14T20:23:50Z")

</div>

Hi @sirwin007, managing complex, multi-tenant hierarchies with row-level security can be challenging, particularly when it comes to filtering based on hierarchy levels. Here is a good [reference article](https://community.amazonquick.com/t/understanding-namespaces-groups-users-and-shared-folder-in-amazon-quicksight/13158).

Here’s a way to address your scenario and ensure that you can apply filters effectively:

### Step-by-Step Solution:

1. **Create Hierarchy Level Columns:**

- Ensure that your dataset includes hierarchy level columns for each tenant. For example, have columns like `Level1`, `Level2`, …, `Level10`, each containing the GUIDs or identifiers for the nodes at that level.

1. **Row-Level Security Setup:**

- Ensure your row-level security dataset correctly defines which GUIDs a user can access.
- This is already set up with a list of GUIDs that each user can see.

1. **Custom Calculation for Display and Filtering:**

- Create a calculated field that combines the level display with the GUIDs, ensuring the GUIDs are hidden or moved off-screen.
- Use the calculated field to push the GUIDs off-screen in a way that they don’t clutter the visual.

1. **Filter Setup:**

- Set up filters in Quick Sight that allow users to select the hierarchy levels.
- Use a custom control or an advanced parameter to manage the filtering logic based on the user’s selection.

### Detailed Implementation:

1. **Create Calculated Fields for Levels:**

- In Quick Sight, go to your dataset and create calculated fields for each hierarchy level, for example:

```auto
Level7_Display = concat('Level 7 ', repeat(' ', 50), guid)
Level8_Display = concat('Level 8 ', repeat(' ', 50), guid)
...

```

1. **Using Parameters and Controls:**

- Create parameters for each hierarchy level, e.g., `selected_level`.
- Create a control (dropdown) linked to this parameter so users can select the level they want to filter by.

1. **Implementing the Filter Logic:**

- Use a calculated field to determine if a data row should be visible based on the selected hierarchy level and the user’s permitted GUIDs.
- For example:

```auto
Can_View_Row = ifelse(
  contains(${permitted_guids}, ${data_row_guid}),
  1,
  0
)

```

1. **Applying the Filter:**

- Apply the `Can_View_Row` calculated field as a filter in your analysis:
  - Add a filter with the condition `Can_View_Row = 1`.

### Example:

Assume we have the following hierarchy and user permissions:

- User can see from Level 7 down to Level 10.
- User has permitted GUIDs: `['guid1', 'guid2', 'guid3']`

**Calculated Fields:**

For Level 7:

```auto
Level7_Display = concat('Level 7 ', repeat(' ', 50), 'guid1,guid2,guid3')

```

For Level 8:

```auto
Level8_Display = concat('Level 8 ', repeat(' ', 50), 'guid1,guid2,guid3')

```

**Filter Logic:**

```auto
Can_View_Row = ifelse(
  contains(['guid1', 'guid2', 'guid3'], ${data_row_guid}),
  1,
  0
)

```

**In Analysis:**

- Add a parameter `selected_level` and link it to a control (e.g., a dropdown with values `Level7_Display`, `Level8_Display`, …).
- Apply a filter where `Can_View_Row = 1`.

By using this approach, you ensure that the user interface remains clean while still allowing the complex filtering logic needed for your multi-tenant hierarchy structure. The display value and GUIDs are managed in such a way that they do not interfere with the visual representation but still support the required filtering functionality.

Did this solution work for you? I am marking this reply as, “Solution,” but let us know if this is not resolved. Thanks for posting your questions on the Quick Sight Community!
