Forum Discussion
Filter a visual using the Role name
Anonymous 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.
Anonymous 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?
Anonymous I did it like the attached. Seperate (disconnected) Countries table:
Countries = DISTINCT(UNION(SELECTCOLUMNS('Table',"Country",[ORIGIN_COUNTRY]),SELECTCOLUMNS('Table',"Country",[DESTINATION_COUNTRY])))RLS Rules:Countries - [Country] = "USA"Table - [ORIGIN_COUNTRY] = "USA" || [DESTINATION_COUNTRY] = "USA"Selection Measure:Measure = IF(MAX('Table'[ORIGIN_COUNTRY]) = MAX('Countries'[Country]),1,0)PBIX is attached. Model, View As, USAHi 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