Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I have a table containing ORIGIN_COUNTRY and DESTINATION_COUNTRY.
I have created a role named "USA", which rule is:
ORIGIN_COUNTRY = "USA" || DESTINATION_COUNTRY = "USA".
Now, I create a visual where I want to see only the data where ORIGIN_COUNTRY = role name. I don't want to "hardcode" the rule, I want the filter to be based on the current role name.
How can I do this?
@federica , did not get completely
https://radacad.com/dynamic-row-level-security-with-organizational-hierarchy-power-bi
Not really. Let's say that these are my data:
ORIGIN_COUNTRY | DESTINATION_COUNTRY
USA | ITA
USA | ITA
JPN | USA
ITA | USA
AUA | JPN
JPN | ITA
I create the role "USA", which only shows records where ORIGIN_COUNTRY ="USA" || DESTINATION_COUNTRY="USA".
When seeing the data as the role "USA", these are the records I can see:
ORIGIN_COUNTRY | DESTINATION_COUNTRY
USA | ITA
USA | ITA
JPN | USA
ITA | USA
Now, I want to create a row-card which only shows the records where ORIGIN_COUNTRY ="USA". How can apply this filter? When trying to do so, these are the possibilities I can filter ORIGIN_COUNTRY with: USA, JPN, ITA. I cannot "hard-select" the value "USA", because otherwise this won't work with the other roles, say JPN. There's no way to do a filter like: ORIGIN_COUNTRY = role_name?
@federica That definitely clears up the confusion. Perhaps try this:
1. Create a disconnected Countries table, just a list of countries. Put your row level security there. Then you could do this:
2. Create a measure
Measure = IF(ORIGIN_COUNTRY =MAX('Countries'[Country]) || DESTINATION_COUNTRY=MAX('Countries'[Country]),1,0)
3. You can now do a page or report level filter for this to 1
4. You can use the same technique at a visualization level for your other requirement
Will try to think of other ideas.
@federica Perhaps another wrinkle on this:
1. Create a disconnected Countries table, just a list of countries.
2. Edit your RLS to be (ORIGIN_COUNTRY ="USA" || DESTINATION_COUNTRY="USA") && 'Country'[Country]="USA"
3. You could then create a measure to filter your visualization like:
IF(ORIGIN_COUNTRY = MAX('Country'[Country]),1,0)
I'm not sure you can do that in RLS but will try to test it.
Hi @Greg_Deckler , I like a lot the second solution but I'm encountering difficulties with the relationship between the COUNTRY table and my table. I ended up creating two COUNTRY tables (one to map origin, another to map destination), linking them with a one-to-many relationship but still I cannot keep both the relationship active and secured. Plus, when I create the role it takes both the conditions as an "&&", so when using the role "USA" I can only see the records where both the origin and the destination are USA. Could you please help?
@federica I did it like the attached. Seperate (disconnected) Countries table:
Hi @Greg_Deckler , thank you! Very much appreciated!
Your solution now is clear, but I've one last remark: it only works when the role is "USA".
The reason is because the MAX('Countries'[Country]) returns USA also when the role is another one, ie the list of values of this column is still [AUA, ITA, JPN, USA] even when I see the report as the role ITA. I am not a DAX expert and I could not find the solution, could you please suggest what to use instead than MAX here? Thanks
@federica - If I understand correctly, you do have to build it into each role. For example, I created an ITA role
RLS Rules
Countries - [Country] = "ITA"
Table - [ORIGIN_COUNTRY] = "ITA" || [DESTINATION_COUNTRY] = "ITA"
I realize that you were essentially kind of wanting to avoid this but I do not see another way because from what I can tell, RLS rules between tables get "anded" together. Maybe if you kept Countries disconnected and made your rules this:
Countries - [Country] = "ITA"
[ORIGIN_COUNTRY] = MAX('Countries'[Country]) || [DESTINATION_COUNTRY] = MAX('Countries'[Country])
Then you would only have to hard code "ITA", "USA", etc once for all of the roles and the other RLS role would be exactly the same between roles.
I see. Well, thank you very much for the help @Greg_Deckler ! It was very helpful!
I've only one last problem, @Greg_Deckler : if I change the visual from table to multi-row card (showing some other data I haven't included in this example), the filter where measure = 1 doesn't work anymore: the menu gets frozen and therefore by defauls selects both measures 0 and 1. What's going on?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
99 | |
98 | |
79 | |
74 | |
66 |
User | Count |
---|---|
136 | |
109 | |
104 | |
82 | |
73 |