Row level security _ DAX

[Anonymous]

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.

[sturlaws]

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]

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]

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]

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/EeH30xo9dm5Lhgb81C...

Thanks in advance for the help,

Mariem,

[sturlaws]

I made some modifications to your file:
sampleFileSecu.pbix 

[Anonymous]

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]

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.

[Anonymous]

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,