Forum Discussion
Filter a visual using the Role name
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?
Anonymous 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
Anonymous - 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!