Manage Security Roles Dax code

[ElenaJ]

I have some Dax code to filter some tables in the Dax code as I use them to manage user access to data. The code is: 

[Project ID] IN

SELECTCOLUMNS (

    DISTINCT(FILTER (UserPermission, [UserPrincipalName] = UserPrincipalName())),

    "Project ID", UserPermission[ProjectID]

)

The dax above were written in the table Project, and UserPermission is a separate table. The problem is that I have a structure in UserPermission Table, users with no [ProjectID] shall see all data, and no need to apply filter. How can the code above be modified accordingly?

[v-yilong-msft]

Hi @ElenaJ ,

I think you can modify your DAX code to include a condition that checks if a user has a [ProjectID] assigned. If not, the filter should not apply to them.

Here is the DAX codes.

VAR CurrentUser = UserPrincipalName()
VAR HasProjectID = NOT ISEMPTY(FILTER(UserPermission, [UserPrincipalName] = CurrentUser && [ProjectID] <> BLANK()))

RETURN
IF(
    HasProjectID,
    [Project ID] IN SELECTCOLUMNS(
        DISTINCT(FILTER(UserPermission, [UserPrincipalName] = CurrentUser)),
        "Project ID", UserPermission[ProjectID]
    ),
    TRUE
)

This code does the following: Checks if the current user has any [ProjectID] assigned in the UserPermission table. If the user has a [ProjectID], it applies the filter based on the projects assigned to them. If the user does not have a [ProjectID], it allows access to all data by returning TRUE.

Best Regards

Yilong Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[ElenaJ]

Thank you!

I found my table actually has 3 layer of user permission structure:

If a user has [ProjectID], they see the data link to [ProjectID].

If the [ProjectID[ is null and a [CompanyID[ is present, then the access should be for all projects that belong to that Company (there is a separate company to project table), called Projecttable.

If both [ProjectID[ & [CompanyID] are null then the user have access to everything.

Is it possible to modify the code as per the above?

[DataInsights]

@ElenaJ

Try an expression like this:

IF (
    // return TRUE if user has no ProjectID (full access)
    ISEMPTY (
        CALCULATETABLE ( UserPermission, [UserPrincipalName] = USERPRINCIPALNAME () )
    ),
    TRUE,
    [Project ID]
        IN SELECTCOLUMNS (
            DISTINCT (
                FILTER ( UserPermission, [UserPrincipalName] = USERPRINCIPALNAME () )
            ),
            "Project ID", UserPermission[ProjectID]
        )
)

Alternatively:

IF (
    // return TRUE if user has no ProjectID (full access)
    ISEMPTY (
        CALCULATETABLE ( UserPermission, [UserPrincipalName] = USERPRINCIPALNAME () )
    ),
    TRUE,
    [Project ID]
        IN CALCULATETABLE (
            VALUES ( UserPermission[ProjectID] ),
            [UserPrincipalName] = USERPRINCIPALNAME ()
        )
)