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

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