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