Help with DAX expression in RLS

[WarrenStaples]

Hi, 

New to powerbi and I was wondering if someone could help with a DAX expression. I have a users table with UserID, Username and a column called FinanceAccess. The FinanceAccess column has values, Y and N. Anyone with Y can access the report and people with N should not have access. 

I'm unsure how to write the DAX here. I've written the following but I get an error message. Thanks for the help. 🙂

[Username] IN
SELECTCOLUMNS (
            FILTER (
             'Users_', 
             'Users_[FinanceAccess])
                = "Y" 
             ),
            "Username", [Username]
)

[Anonymous]

Hi Mate,

You can try the following expression: 

M_RLS = CALCULATE(VALUES('Table'[Country]),FILTER('Table','Table'[Person]="Y" && 'Table'[Country]=SELECTEDVALUE(Sheet1[Country])))

This table is mapping table and its connected to main table with 1:Many relationship.

I received expected output.

[selimovd]

Hey @WarrenStaples ,

where do you want to use that?

Is that the criteria for a role?

Is username the e-mail address? Then you could use USERNAME() or USERPRINCIPLENAME() to check against the table.

If you need any help please let me know.

If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards

Denis

Blog: WhatTheFact.bi

[WarrenStaples]

@selimovd Yes, I would like to use Username() to check against the table and use in the manage roles section. Basically anyone with FinanceAccess marked as Y should have access to the report. 

[selimovd]

Hey @WarrenStaples ,

then use the following DAX code for the row level security:

Users_[Username] = USERNAME() && Users_[FinanceAccess] = "Y"

If you need any help please let me know.

If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards

Denis

Blog: WhatTheFact.bi

[WarrenStaples]

@selimovd Thank you. It doesn't however seem to have any effect on the data. So with flag Y, users should see all data in Sales Table, with Flag N they shouldn't see anything. 

I've included my file here: File 

[selimovd · Accepted solution]

Hey @WarrenStaples ,

the problem is the filter direction in the data model.

Check the following criteria for the sales table:

[NetworkID] in 
SELECTCOLUMNS (
    FILTER (
        Network,
        RELATED ( Users[Username] ) = USERPRINCIPALNAME () && RELATED(Users[FinanceAccess]) = "Y"
    ),
    "NetworkID", Network[NetworkID]
)

If you need any help please let me know.

If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards

Denis

Blog: WhatTheFact.bi

[WarrenStaples]

@selimovd Thanks Denis. And what do you have in the Roles in the User table?  I have the following below but for example, Sam in the table doesn't see anything although he has a Y flag. Perhaps you could attach the file you worked on above? Thanks again.

[selimovd]

Hey @WarrenStaples ,

Sam has UserID 8:

He doesn't see anything because in the related table he doesn't have any data or network connected:

From your first description you wanted 2 criteria:

1. The users see the data they have access to. Sam doesn't, so he won't see anything

2. If the user has a "Y" on Finance Access he is in addition allowed to see the financial data from table Sales.

Please let me know if I understood anything wrong.

If you need any help please let me know.

If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards

Denis

Blog: WhatTheFact.bi

[WarrenStaples]

Hi Denis, You're right. Thank you. 

[selimovd]

Hey @WarrenStaples ,

no problem. I'm happy it worked eventually 🙂

If you need further help just let me know.

If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards

Denis

Blog: WhatTheFact.bi