Dear all,
Currently I'm working on a dashboard that has a hierarchy and RLS. The hierarchy contains 10 levels.
The users are limited to only see the levels they're assigned to. The catch, however, is that the levels above the lowest level they can see should remain fully populated. My data is coming from Teradata with import function.
A simplified structure looks like this:
| Level1 | Level2 | Level3 | … | Value1 | Value2 | … |
| A | AA | AAA | … | 10 | 5% | … |
| A | AA | AAB | … | 20 | 2,50% | … |
| A | AB | ABA | … | 10 | 2,52% | … |
| A | AB | ABB | … | 10 | 1,44% | … |
| A | AC | ACA | … | 20 | 2% | … |
| A | AC | ACB | … | 30 | 3,50% | … |
| B | BA | BAA | … | 25 | 6,10% | … |
In the visual that would be (value1 sum, value2 avg)
| Level | Value1 | Value2 |
| A | 100 | 2,83% |
| AA | 30 | 3,75% |
| AAA | 10 | 5,00% |
| AAB | 20 | 2,50% |
| … | … | … |
Let's say we have user John, John was assigned level3 AAB and ABA. With RLS up and running, he'll only see:
| Level1 | Level2 | Level3 | … | Value1 | Value2 | … |
| A | AA | AAB | … | 20 | 2,50% | … |
| A | AB | ABA | … | 10 | 2,52% | … |
| Level | Value1 | Value2 |
| A | 30 | 2,51% |
| AA | 20 | 2,50% |
| AAB | 20 | 2,50% |
| AB | 10 | 2,52% |
| ABA | 10 | 2,52% |
However, what I want John to see:
| Level1 | Level2 | Level3 | … | Value1 | Value2 | … |
| A | AA | AAB | … | 20 | 2,50% | … |
| A | AB | ABA | … | 10 | 2,52% | … |
| Level | Value1 | Value2 |
| A | 100 | 2,83% |
| AA | 30 | 3,75% |
| AAB | 20 | 2,50% |
| AB | 20 | 1,98% |
| ABA | 10 | 2,52% |
So, what Power BI does, is take the lower levels to 'construct' the higher levels. By applying RLS, not all levels are available and thus, not all data to create the correct numbers.
What I'm looking for, is a way to 'visually' limit the levels for the user and not limit the data.
Alternatively, I can have my values per level determined on data side. In that case I would need a way to have a hierarchy in Power BI without it applying 'logics' to my data.
Thanks in advance.
