Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Filter a visual using the Role name

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...
Need Help
Greg_Deckler's avatar
Greg_Deckler
Community Champion
6 years ago

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.

Anonymous's avatar
Anonymous
Not applicable
6 years ago

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's avatar
    Greg_Deckler
    Community Champion
    6 years ago

    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, USA
    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      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's avatar
        Greg_Deckler
        Community Champion
        6 years ago

        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.