Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Need help on row level security to write dax

Hello all,

I have below 4 tables and fact tables can't be joined with account table.However, I have some other fact table which is joined with account.

Fact_table:
countryid filename

account:
accoutid countryid

user_account:
accuntid userid

user:
userid name


account(1) and user_account(N) has 1:N relationship on accountid
user_account(N) and user(1) has N:1 relationship on userid

I can not join Fact Table and account since I don't have accountid in my fact table or else its easy to just apply RLS on user table (username=userprinciplaname()).

I have to apply RLS on fact_table(not having relationship with account) on column called "countryid" so that the user can see data for country which they belong to.


/*below sql code should be in row level security DAX Function */

select * from fact_table where countryid in (select countryid from account where account with user via user_account and user[name]=userprinciplaname())

Please help and thanks in advance!

Need Help

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    select * from fact_table where

    countryid IN (
    select countryid from account a
    JOIN user_account ae ON a.accountid = ae.accountid
    JOIN user e ON ae.userid = e.userid and user=userprincipalname() )
    Jihwan_Kim amitchandak : please take a look into this and help me with writing dax 
    Any help much appreciated!! Thanks in advance

    • selimovd's avatar
      selimovd
      Icon for Most Valuable Professional rankMost Valuable Professional
      5 years ago

      Hey Anonymous ,

       

      you cannot just convert an SQL query to DAX.

      I still didn't understand what is your problem and how the result should look like. Maybe you can show a data model instead of a long text that is hard to understand.

      Then please describe at what point you are struggling and what result you want. That would make it easier to help you. But with the text above, I don't know what you want.

       

      Best regards

      Denis