DAX measure for getting the values from another column only if the condition is true

[LP2803]

Hi Team,

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.

[LP280388]

@Greg_Deckler I shared the sample data above. Kindly help.

[v-rzhou-msft]

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 10002@xyz.com  , I can see all records.

When I view as 10005@xyz.com  , I can see zero record.

Best Regards,
Rico Zhou

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

[LP280388]

@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]

Hi @Greg_Deckler thank you. but I dont want to concatenate the values. I want to show in different rows for different values.

[Greg_Deckler]

@LP2803 Would need to see sample data. You might be able to use a Complex Selector but can't be certain. 

The Complex Selector - Microsoft Power BI Community

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.

[LP2803]

@Greg_Deckler 

Below is the sample data.

AD Group table
Emp ID	emailid
10001	10001@xyz.com
10002	10002@xyz.com
10003	10003@xyz.com

Sales table
Emp ID	sale date
10004	01-01-22
10006	02-01-22
10007	03-01-22
10007	04-01-22
10008	05-01-22
10008	06-01-22
10008	07-01-22

 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
10004	01-01-22
10006	02-01-22
10007	03-01-22
10007	04-01-22
10008	05-01-22
10008	06-01-22
10008	07-01-22

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]),"")

[Greg_Deckler]

@LP2803 Use CONCATENATEX