Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
ElenaJ
Frequent Visitor

Manage Security Roles Dax code

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?
3 REPLIES 3
v-yilong-msft
Community Support
Community Support

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.

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
Super User
Super User

@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 ()
        )
)

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.