Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Row level security _ DAX

Hello    I’m trying to implement row level security using DAX, I managed to implement some parts but I’m stuck with a small issue and would appreciate your help. I have in my report 3 tables with ...
Need Help
sturlaws's avatar
sturlaws
Resident Rockstar
6 years ago

Hmm,

 

you have 2 relationship which might be a bit troublesome:

1. The inactive many-to-many-relationship between TD_SECU_SECTOR and V_SECU_REGION2. Many-to-many is a potential minefield in PBI.

2. The bidirectional relationship between TD_REGION and V_SECU_REGION2.

 

I have created a simple demo report to show how I would have solved it:

demo 

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Hi sturlaws 

 

Thank you for the advices and your time.  I tired to avoid the bidirectional relationship, but in this context, I needed one.

 

I also tried to implement your solution, but it didn't work (when I test it with username = "test")

I also didn't understand the logic behind it and I think my schema is a bit more complicated than the sample file you provided me. I work with a galaxy schema.

 

I have made a sample file more specific to my context 

https://liveeduisegiunl-my.sharepoint.com/:u:/g/personal/m20180240_novaims_unl_pt/EeH30xo9dm5Lhgb81C42-hUBLtaQLQT87DtumLZ8n_ZWdw?e=YBWiWg

 

Thanks in advance for the help,

Mariem,

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hello sturlaws 

       

      Thanks for your help. I do have the wanted result when I test it with user "test".

      But when I test it with other users which are supposed to have specific access to some sector,  they actually get access to all sectors.

       

       

       
       

       

      • sturlaws's avatar
        sturlaws
        Resident Rockstar
        6 years ago

        My bad, I thought that what were what you wanted. Then the case is"See your own sector and the total of your region", which is a common scenario.

         

        You have three options:

        1. If you are making the reports, and the data model is not meant for self service or exploration, you can create a measure to filter e.g. the table visual in your previous post for the sector a user is supposed to see:

        sectorFilterMeasure =
COUNTROWS (
    CALCULATETABLE (
        VALUES ( TD_SECTOR[SECTOR_ID] );
        FILTER (
            TD_SECTOR;
            TD_SECTOR[SECTOR_ID] IN VALUES ( TD_SECU_SECTOR[SECTOR_ID] )
        )
    )
)

         

        2. Have two sets of fact tables, one set with granularity of sector, the other set with granularity region.

         

        3. Have two sets of measures, one set which returns the sectors for current user, the other set which returns region for current user.

         

        The code needed for the last two options will resemble the RLS filter expressions.