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:  A employee view where the report filters my table based on employee email A supervisor view where the report filters my table based ...
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.

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.