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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
LP2803
Responsive Resident
Responsive Resident

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

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.

7 REPLIES 7
LP280388
Resolver II
Resolver II

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

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

RicoZhou_0-1649646304828.png

When I view as 10002@xyz.com  , I can see all records.

RicoZhou_1-1649646324807.png

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

RicoZhou_2-1649646340278.png

 

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.

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

 

LP280388_0-1649698989892.png

 

 

LP2803
Responsive Resident
Responsive Resident

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

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


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

@Greg_Deckler 

Below is the sample data.

AD Group table 
Emp IDemailid
1000110001@xyz.com
1000210002@xyz.com
10003

10003@xyz.com

 

Sales table 
Emp IDsale date
1000401-01-22
1000602-01-22
1000703-01-22
1000704-01-22
1000805-01-22
1000806-01-22
1000807-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 IDsale date
1000401-01-22
1000602-01-22
1000703-01-22
1000704-01-22
1000805-01-22
1000806-01-22
1000807-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
Super User
Super User

@LP2803 Use CONCATENATEX


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
Fabcon_Europe_Social_Bogo

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.