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

[RajaKoteru]

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

[RajaKoteru]

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

[muhssamy]

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 

[RajaKoteru]

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

[RajaKoteru]

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]

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