'Need DAX for RLS in Power BI where one user sees their own countries data or manager sees all countries data means no RLS
I have to implement dynamic RLS on country where one user is allowed to see their own country data and if user is manager(IsManager=Yes)they should see all countries data means no RLS.
Employee-Country table looks like below
| UserID | Email Address | Country | IsManager |
|--------|---------------|------------|-----------|
| 1 | [email protected] | Finland | No |
| 2 | [email protected] | Italy | No |
| 3 | [email protected] | Spain | No |
| 4 | [email protected] | Belgium | No |
| 5 | [email protected] | AllCountry | Yes |
| 6 | [email protected] | AllCountry | Yes |
| 7 | [email protected] | Finland | No |
| 8 | [email protected] | Belgium | No |
| 9 | [email protected] | Finland | No |
Country Table
| Country | CountryID |
|---------|-----------|
| Finland | 1 |
| Belgium | 2 |
| Italy | 3 |
| Spain | 4 |
Fact Table
| FactID | Country | Amount |
|--------|---------|--------|
| a | Finland | 100 |
| b | Italy | 200 |
| c | Spain | 300 |
| d | Belgium | 400 |
| e | Belgium | 200 |
| f | Italy | 150 |
| g | Spain | 300 |
| h | Spain | 390 |
| i | Belgium | 400 |
Data Model looks like
Employee Country and Country is Joined on Country column
Fact table is joined with Country on Country column
I am trying to add DAX on Country table
[Country] IN
SELECTCOLUMNS(
FILTER(
'Employee Country',
'Employee Country'[Email Address]=userprincipalname()
),
"Country",[Country])
My question is where exactly I pass this condition to check if user is manager then show all country data(no RLS)
Please help me with DAX or better way to handle this (I am trying to avoid many-many relationship + bidirectional)
Any help would be much appreciated!
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|