Filter a visual using the Role name

[federica]

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?

[amitchandak]

@federica , did not get completely

https://community.powerbi.com/t5/MBAS-Gallery/Microsoft-Power-BI-Unleash-row-level-security-patterns...

https://radacad.com/dynamic-row-level-security-with-organizational-hierarchy-power-bi

[Greg_Deckler]

I may be mis-understanding @federica but that is default behavior when RLS is implemented.

[federica]

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?

[Greg_Deckler]

@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.

[Greg_Deckler]

@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.

[federica]

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?

[Greg_Deckler]

@federica 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, USA

[federica]

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

[Greg_Deckler]

@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.

[federica]

I see. Well, thank you very much for the help @Greg_Deckler ! It was very helpful!

[Greg_Deckler]

Hope were were able to get to a resolution @federica !

[federica]

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?