I have a table from which I would like to show the rows only to certian users who are part of the AD group.
I have two tables,
1. AD group with AD group name, empid, emailid
2. Sales data with empid, request_id, creationdate
I'm looking to take the currentuser logged into the system with "userprinciplename()" and check if the user is available in the AD Group table, if yes, then give the values else return null
Below is the DAX query I have currently.
measure = if(contains(Adgroup,Adgroup[emailid],userprinciplename()),max(sales[request_id]),"")
But this gives only one records as expected because of the max() used.
In case of multiple request_ids for a employee, how can i retrieve all the records without this aggregation?
I can't use a calculate columns as "userprinciplename() cant be used in calculated column.
The RLS doesnt work in my case as this is for the users who are at the "Contributor" level.
Hi @LP2803 ,
I think you can try this code in "Sales" table in Manage Roles.
[Emp ID] = IF ( USERPRINCIPALNAME () IN VALUES ( 'AD Group'[emailid] ), [Emp ID], BLANK ()
When I view as email@example.com , I can see all records.
When I view as firstname.lastname@example.org , I can see zero record.
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@v-rzhou-msft thanks for the detailed response. I'm getting the below error as i have a Direct Query and i have both tables from oracle DB. any other alternative please. The reason I cant use the Import mode is because I need to restrict the users (who know powerbi and has contributor access) from downloading the file from workspace and using the restricted data.
@LP2803 Would need to see sample data. You might be able to use a Complex Selector but can't be certain.
Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882
Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.
Below is the sample data.
|AD Group table|
|Emp ID||sale date|
Expected Output with DAX is:
when user 10002 logs in, he should see 2 records and 3 records for 10007 & 10008 respectfully.
when user 10005 who is not part of the AD group should see zero records
|Emp ID||sale date|
with the DAX measure I currently have, only gives the max(sale date) from the sale table for 10007 and 10008 instead of all rows.
measure = if(contains(ADgroup,Adgroup[emailid],userprinciplename()),max(sales[sale date]),"")
Check out the November 2023 Power BI update to learn about new features.
Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.
130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.