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