Create a filter - own department & other departments

[jhaast]

Hi people,

I am trying to make a report which makes it possible to show the deparment results(e.g. Department "Maintenance") and other visuals show only the result of the subdepartment of the user of the report (e.g.  Department "Maintainence" - SubDepartment "Team 1").

The purpose is to add both visuals of the report to a dashboard and based on the row level security should the visuals differ for each user.

Table's:

Department:

Department	SubDepartment	Departmentgroup
Maintainence	Team 1	Own departement
Maintainence	Team 2	Other department
Maintainence	Team 3	Other department
Production	Team 1	Other department

User:

Username	Department
User 1	Team 1
User 2	Team 3

I want solve this challange to create the column DepartmentGroup with two values: "own department" and "other departments". This values do I want to define based on the User table. 

So for user 1 should the Department table look like this:

Department	SubDepartment	Departmentgroup
Maintainence	Team 1	Own departement
Maintainence	Team 2	Other department
Maintainence	Team 3	Other department
Production	Team 1	Other department

And for user 2:

Department	SubDepartment	Departmentgroup
Maintainence	Team 1	Other departement
Maintainence	Team 2	Other department
Maintainence	Team 3	Own department
Production	Team 1	Other department

Can somebody tell me how I can create the column Departmentgroup or an other option to create visuals based on the whole department or only the subdepartment?

Thank you in advance!

[v-janeyg-msft]

Hi, @jhaast 

There are two departments where team1 is on. How to judge that user 1 is in ‘Maintainence’ instead of ‘Production’ ?

You can use RLS to let users view only their own department data. You need to define roles and rules within Power BI Desktop and set in service.

Reference: Row-level security (RLS) with Power BI - Power BI | Microsoft Docs

You can follow the document first, please feel free to ask me if you don’t understand.

Best Regards

Janey Guo

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

[jhaast]

Hi Janey,

No there is a maintenance team 1 and a production team 1. Those are two different teams.  The user table was missing a third column and should look like:

However, I want to create a calculated column with the values "own subdepartment" and "other subdepartement" in the department table, based on the deparment and subdepartment of the logged in User.
If user 1 logs in then the table should look like:

Department	SubDepartment	Departmentgroup
Maintainence	Team 1	Own departement
Maintainence	Team 2	Other department
Maintainence	Team 3	Other department
Production	Team 1	Other department

So the user 1 logs in and his Deparment + Subdepartment combination will get the label "Own department" and the other teams will get the label "Other department".

And if user 2 logs in, the same table should look like:

Department	SubDepartment	Departmentgroup
Maintainence	Team 1	Other departement
Maintainence	Team 2	Other department
Maintainence	Team 3	Own department
Production	Team 1	Other department

The total purpose of this challange is to create visuals for the whole deparment of the user and visuals only for the user. The user haves permissions to the data of the whole deparment (based on RLS). Some visuals should only show his/her numbers and that visual should also change for another user.

Those this clearify the issue?

[v-janeyg-msft]

Hi, @jhaast 

You can try to put RLS field(Department,SubDepartment) to slicer, then create a measure like this:

Departmentgroup =
IF (
    table[Department] = SELECTEDVALUE ( table[Department] )
        && table[SubDepartment] = SELECTEDVALUE ( table[SubDepartment] ),
    "Own departement",
    "Other department"
)

Maybe helps. If it doesn’t work, it’s probably not supported.

Best Regards

Janey Guo

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

[jhaast]

Can somebody help me? 😇