Forum Discussion

nsj2113's avatar
nsj2113
Frequent Visitor
10 years ago
Solved

USERNAME() function DAX

Hi,   I am trying to use Row Level Security for my Power Bi model and for that I am using username() function to get the filters. The thing is that I have two filters to pass and thus would like my...
Need Help
  • chrisu's avatar
    chrisu
    10 years ago

    Not quite sure if this is what you're looking for, but I do something similar using LOOKUPVALUE.  Basically I find where USERNAME() matches the Email in the Users table and then filter the Programs table where either the returned Program 1 or Program 2 match.  The filters on the Programs table then flow to all the other tables in my model.  

     

    Programs

    Program

     

    Users

    User 1     Email     Program 1     Program 2

     

    My RLS set up in the Programs table is: 

     

    [Program] = LOOKUPVALUE(Users[Program 1], [Email], USERNAME()) || [Program] = LOOKUPVALUE(Users[Program 2], [Email], USERNAME())

chrisu's avatar
chrisu
Icon for Responsive Resident rankResponsive Resident
10 years ago

Not quite sure if this is what you're looking for, but I do something similar using LOOKUPVALUE.  Basically I find where USERNAME() matches the Email in the Users table and then filter the Programs table where either the returned Program 1 or Program 2 match.  The filters on the Programs table then flow to all the other tables in my model.  

 

Programs

Program

 

Users

User 1     Email     Program 1     Program 2

 

My RLS set up in the Programs table is: 

 

[Program] = LOOKUPVALUE(Users[Program 1], [Email], USERNAME()) || [Program] = LOOKUPVALUE(Users[Program 2], [Email], USERNAME())

  • nsj2113's avatar
    nsj2113
    Frequent Visitor
    10 years ago

    Thanks that worked perfectly. 

  • bises's avatar
    bises
    Frequent Visitor
    8 years ago

    This solution works perfectly but I was confused on the second part "The filters on the Programs table then flow to all the other tables in my model"

     

    I am trying to filter other tables with no user info in them but has relation to programs. And I try to filter other table based on the programs table I am not able to filter that. 

  • bises's avatar
    bises
    Frequent Visitor
    8 years ago

    This solution works perfectly but I was confused on the second part "The filters on the Programs table then flow to all the other tables in my model"

     

    I am trying to filter other tables with no user info in them but has relation to programs. And I try to filter other table based on the programs table I am not able to filter that.