Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Supervisor Role Filtering

Hey there, 

 

I have a report that I want to have two views: 

  1. A employee view where the report filters my table based on employee email
  2. A supervisor view where the report filters my table based on the supervisor email

I have a table with all employee emails and their manager emails, and a table that can toggle between the two modes.

I've tried creating a new column in my employee table "if(selectedvalue(supervisor mode)=True, manger email, email)" with no luck. It only returns the employee email. 

 

The employee table (employee and supervisor data) is used in relationships to several other tables. Ideally, the supervisor mode will filter the employee table and therefore filter the tables it has a relationship to. 

 

Any help is appreciated, been hitting my head against a wall trying to figure it out. 

 

 

 

 

Need Help
  • v-alq-msft's avatar
    v-alq-msft
    5 years ago

    Hi, Anonymous 

     

    Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

    Table:

     

    Mode:

     

    You may create a measurea as below.

    Visual Control = 
var emp = SELECTEDVALUE('Table'[Employee],"")
var sup = SELECTEDVALUE('Table'[Supervisor],"")
return
IF(
    ISFILTERED(Mode[Mode]),
    IF(
        "employee mode" in DISTINCT(Mode[Mode])&&NOT("supervisor mode" in DISTINCT(Mode[Mode])),
        IF(
            emp=USERPRINCIPALNAME(),1,0
        ),
        IF(
            "supervisor mode" in DISTINCT(Mode[Mode])&&NOT("employee mode" in DISTINCT(Mode[Mode])),
            IF(
                sup=USERPRINCIPALNAME(),
                1,0
            ),
            IF(
                "employee mode" in DISTINCT(Mode[Mode])&&"supervisor mode" in DISTINCT(Mode[Mode]),
                IF(
                    OR(
                        emp=USERPRINCIPALNAME(),
                        sup=USERPRINCIPALNAME()
                    ),
                    1,0
                )
            )
        )
    )
)

     

    Then you need to put the measure in the visual level filter and use 'Mode' column from 'Mode' table to filter the result.

     

    Finally you may view as '[email protected]'. Here are the results.

     

    Best Regards

    Allan

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

6 Replies

  • v-alq-msft's avatar
    v-alq-msft
    Community Support
    5 years ago

    Hi, Anonymous 

     

    Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

    Table:

     

    Mode:

     

    You may create a measurea as below.

    Visual Control = 
var emp = SELECTEDVALUE('Table'[Employee],"")
var sup = SELECTEDVALUE('Table'[Supervisor],"")
return
IF(
    ISFILTERED(Mode[Mode]),
    IF(
        "employee mode" in DISTINCT(Mode[Mode])&&NOT("supervisor mode" in DISTINCT(Mode[Mode])),
        IF(
            emp=USERPRINCIPALNAME(),1,0
        ),
        IF(
            "supervisor mode" in DISTINCT(Mode[Mode])&&NOT("employee mode" in DISTINCT(Mode[Mode])),
            IF(
                sup=USERPRINCIPALNAME(),
                1,0
            ),
            IF(
                "employee mode" in DISTINCT(Mode[Mode])&&"supervisor mode" in DISTINCT(Mode[Mode]),
                IF(
                    OR(
                        emp=USERPRINCIPALNAME(),
                        sup=USERPRINCIPALNAME()
                    ),
                    1,0
                )
            )
        )
    )
)

     

    Then you need to put the measure in the visual level filter and use 'Mode' column from 'Mode' table to filter the result.

     

    Finally you may view as '[email protected]'. Here are the results.

     

    Best Regards

    Allan

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Hey Allan! v-alq-msft 

       

      Thanks for the help, I adapted your code to my PBIX. IS there anyway to adapt the solution to have the mode directly filter the Employee Table inplace of just a visual filter?

       

      I neglected to mention that my Employee table is related to several other tables that it filters. 

       

       

      • v-alq-msft's avatar
        v-alq-msft
        Community Support
        5 years ago

        Hi, Anonymous 

         

        I don't think you can adapt the solution to have the model directly filter the Employee Table. You may try using query parameter to filter the model in Power Query. However, you are unable to get the current user e-mail to filter the specific user.

         

        About query parameters in Power BI, you may refer to the following links.

        Deep Dive into Query Parameters and Power BI Templates 

        Power BI Desktop Query Parameters 

         

        Best Regards

        Allan

         

        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi Anonymous - data seems incomplete. What i understood is you have 2 different tables? Employee and Supervisor, how are they related/Linked to each other?

     

    Can you give some sample data and their relation please? like the one below

     

     

    Thanks,

    -Namish B

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    You could use the following 

     

    Create 3 tables:

    table 1:  "User list" (unique email id)

    table 2 : "employee view"

    table 3 :  "supervisor view"

    Table 2 and 3 are coipies of each other.

     

    Then you can create relationships

    1. user list with employee email in table 2

    2. user list with supervisiou email in table 3

     

    Then apply RLS on table 1 user. 

     

    This way you can create 2 views. The views using columns from table 2 will be the "employee view" and the views using columns from the table 3 will be the "supervisor view"

     

    PS: Please mark this post as a "solution"  if this helps solving the issue and/or hit the "thumbs-up" button if you found this insighful. Thanks.

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Hey Helper !

       

      If I understand your solution correclty I would have to apply the duplication process to all of the tables that my employee table is related to. Yah? (I neglected to mention these relationships in the original description)

       

      Appreciate the feedback !