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 different measures, one on the regional level, one the sector level and one on the district level (region -> sector ->  district)

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Let’s suppose I have 3 regions, each composed of 3 sectors (i.e : region A -> sector A1, A2 and A3)

Users either have acces to certain sectors (of the same region) or to a region or more. When a user has access to a sector he also needs to see the values on the regional level to which that sector belongs to.

I managed to implement that using the following formula

 

 

In case a user have access to region, he needs to see of all sectors composing that regions. In my current model, I have a table V_SECU_REGION where I specify the user name and the region ID and another table TD_SECU_SECSTOR where I specifiy the user name and the sector ID.

 

In my current model, when a user has access to one region and all sectors, his username is only mentioned in the V_SECU_REGION table or not in the TD_SECU_SECTOR. I would like to keep my model this way and not assign all sectors to the user in the TD_SECU_SECTOR

So I have tried to implement a dax formula that tries to look into the table TD_SECTOR and gets all the sectors that compose that region.

 

SectorID = SELECTCOLUMNS(FILTER(TD_SECTOR; TD_SECTOR[REGION_ID] = VALUES(V_SECU_REGION[REGION_ID])); "SectorID" ; TD_SECTOR[SECTOR_ID]))

 

However, it only returns blank values.

I don’t understand what I’m doing wrong, does anyone have any suggestions.

Need Help

8 Replies

  • sturlaws's avatar
    sturlaws
    Resident Rockstar
    6 years ago

    Hi Anonymous 

    Your screenshot does not show all the fields of the various tables, and I can't see which fields are used to create the relationships, so this is a bit of guess work. Could you add this to the V_SECU_SECTOR-table

    RegionID in
CALCULATETABLE (
    VALUES ( V_SECU_REGION[RegionID] ),
    FILTER ( V_SECU_REGION; V_SECU_REGION[User_ID] = USERPRINCIPALNAME () )
)


    You can also use this together with other conditions, just use || between the statements

     

    Cheers,
    Sturla

    If this post helps, then please consider Accepting it as the solution. Kudos are nice too.

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      hello sturlaws 

       

      Here is a new screenshot, more clear one. 

      Thank you for the code, but that's not what I actually need. I also have that part working using this code:

       

      [REGION_ID] IN
      distinct(
      SELECTCOLUMNS(

      FILTER('V_SECU_REGION2',
      'V_SECU_REGION2'[USER_ID] = USERPRINCIPALNAME()),

      "RegionID" , 'V_SECU_REGION2'[REGION_ID])
      )

       

      let's say for instance i have 2 users, then my tables would like this:

       

      V_SECU_REGION2:

      Index  USER   RegionID/LB

      1        user1    RegionA

      2       user2     RegionB

       

      TD_SECU_SECTOR

      Index    USER    SectorID

      1           user2    SectorB01

       

      When i have my tables set like this, it means that my user1 is supposed to have access to all sectors composing regionA (Sector A1, A2, A3,..)

      In my current security, I'm able to apply security for user2 but for user1, I can't manage to show all the sectors. I hope this screenshot will be explain further what i'm looking for:

       

       

      I thought about doing something like this to put in place security:

      IF ( user from V_SECU_REGION exists in both tables; 

           then get sector_ID from table TD_SECU_SECTOR that will filter TD_secotor that will filter my fact table [already works]

          else get sector_ID from TD_SECTOR that will filter my fact table [stuck at this part ] )

       

      For the last part, I wrote this code, thinking in this case, I will be able to get my sector_ID from TD_SECTOR, but it doesn't work.

      SectorID = SELECTCOLUMNS(FILTER(TD_SECTOR; TD_SECTOR[REGION_ID] = VALUES(V_SECU_REGION[REGION_ID])); "SectorID" ; TD_SECTOR[SECTOR_ID]))

       

      I hope I managed to explain better my issue,

      Regards,

      Mariem,

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hello sturlaws 

       

      Here is a more clear screenshot  I actually don't have the regionID in my TD_SECU_SECTOR table however, however, I have added your code to my V_SECU_REGION2 table and it doesn't give me the results I need.

       

      Let's say I have 2 users and 2 regions, each composed of 3 sectors, then my tables would look like this:

      V_SECU_REGION2                                                                 TD_SECU_SECTOR 

      index   user   regionID                                                     index     user   sectorID

      1        user 1    regionA                                                      1       user2    sectorB1

      2        user2     regionB                                                       2      user2    sectorB3

       

      It means that my user1 is supposed to see all sectors of regionA, here is more screenshot to explain my current situation

         

      I was thinking about using an IF statement to apply security that behaves in this way:

      IF ( user from V_SECU_REGION2 exists in TD_SECU_SECTOR 

          THEN   get sectorID from TD_SECU_SECTOR  which will filter my TD_SECTOR which will filter my fact table

          ELSE get sectorID directly from TD_SECTOR which will filter my fact table [can't get the code to work correctly]  )

       

      I thought of using this code when I have a case like user1 but it doesn't work: 

      SectorID = SELECTCOLUMNS(FILTER(TD_SECTOR; TD_SECTOR[REGION_ID] = VALUES(V_SECU_REGION2[REGION_ID])); "SectorID" ; TD_SECTOR[SECTOR_ID])
       
      I hope I have explained it better,
      Regards,
      Mariem,
      • 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