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
RajaKoteru
Frequent Visitor

How to send USERPRINCIPALNAME to dynamic query to return results based on logged in user

Hi,

I have a requriement to show data based logged in user. The logged in user (USERPRINCIPALNAME) need to be passed to query as  a parameter user_id to the backend direct query. Data is huge cannot do import mode.

 

For example --

select sec_lev_grant from table  where upper(user_id) = upper('user_id@domain.com')

 

Thanks in advance

5 REPLIES 5
RajaKoteru
Frequent Visitor

Thank you for the quick response.

 

What is the fact ?

 

Here's what I have 

CUser = USERPRINCIPALNAME()
I need to pass this Cuser value to my dynamic query  as below
select sec_lev_grant from table  where upper(user_id) = upper('&CUser& ')
 
I cannot use import for table...because it has huge data. I have to use dynamic query mode.
 
Please provide more details on how to pass the USERPRINCIPALNAME() value to the query

yes. dont import your table. and dont use dynamic query. just create a table contains IDs and use it also in import mode and let the relationship do the filtrations

something like the following image

 

USERPRINCIPALNAME() will filter the dimention that will have a join in your database with the right id 

muhssamy_0-1721283156223.png

 

This solution didn't work. Not sure If I am missing anything.  Can you please provide more details.

Thank you for the reply. Without dynamic query I am confused how it filters the data.

Is fact a table that we get the results from the query and UserDim is the measure  ?

muhssamy
Resolver I
Resolver I

1- create a table contains the user emails in your database

2- make a relationship in power bi datamodel between this table and your fact

3- filter the new table using Row level security.

 

this will achive your Goal

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.