Permissions in the Role Manager with DAX

[coriben]

Hello, I’m trying to configure the permissions on my report to use Row Level Security in the Embedded version later.

I have the following table structure (This will be improved later, right now I’m trying to make it work in Power BI Desktop using the Roles Manager):

Users Table:

User_ID               Username

1                             User 1

2                             User 2

Permissions Table (These are our Client’s companies branches. Some of their employees can only see data for their own branch):

Permission_ID  Branch_Name

1                             Branch 1

2                             Branch 2

Bridge table between them (As you see, User 1 can see data for both branches):

ID           User_ID               Permission_ID

1             1                             1

2             1                             2

3             2                             2

And my data table. Every row of my data table has a “Branch” column. What I need is:

1. Get what Branches each user can access ( username will be supplied by USERNAME() function);
2. Filter the data table with said Branches;

I’ve managed to do it easily using actual filters (throwing the data at tables in the canvas and clicking), but I’m struggling to get it to work inside the Roles Manager.

Is this only possible with DAX? Is there an easier way?

Thanks!

[v-jiascu-msft]

Hi @coriben,

1. Establish proper relationships.

2. Create roles like below.

[User_ID] = 1

3. Assign the role to users.

What's your scenario? App owns data or User owns data?

Best Regards,

Dale

[coriben · Accepted solution]

Hello, thanks for your input. It's an App owns Data scenario, but I got it to work.

First, I included 2 fields in the bridge table so I don't need the Users and permissions tables anymore:
Username and Branch_name.

Then, the following DAX that I found did it:

[Data_table_branch_column]
    IN CALCULATETABLE (
        VALUES ( 'bridge_table'[Branch_name] ),
        FILTER (
            ALL ( 'bridge_table' ),
            [Username] = USERNAME ()
                || [Username] = USERPRINCIPALNAME ()
        )
    )

[Anonymous]

Hello

Can I use user permission on the Power BI Desktop (Optimized for Power BI Report Server), if yes how?