Forum Discussion

RajaKoteru's avatar
RajaKoteru
Frequent Visitor
2 years ago

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('[email protected]')

 

Thanks in advance

Need Help

5 Replies

  • muhssamy's avatar
    muhssamy
    Resolver I
    2 years ago

    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

  • RajaKoteru's avatar
    RajaKoteru
    Frequent Visitor
    2 years ago

    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's avatar
      muhssamy
      Resolver I
      2 years ago

      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's avatar
        RajaKoteru
        Frequent Visitor
        2 years ago

        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  ?