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
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:
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?
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 ()
)
)
Proud to be a Super User!
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
115 | |
112 | |
105 | |
95 | |
58 |
User | Count |
---|---|
174 | |
147 | |
136 | |
102 | |
82 |