Filter data based on slicer selection

[Anonymous]

Hi there,

I am creating a manager dashboard with HR (people) data and I am having issues with filtering the dataset based on manager selection. There is of course RBP set up, so the principal user of the dasboard can only see his/her part of the organization based on employee-manager path (created via their IDs). However, the principal user might want to select a lower ranking manager and see what they see, e.g. a VP wanting to look into certain parts of the organization. For this purpose, I have added a slicer with manager names and based on the name selected, I have created a measure, Selected Manager, that calculates the selected manager's ID.

Selected Manager = IF((SELECTEDVALUE(Users[Manager Name]) = BLANK()), [Current Manager ID], LOOKUPVALUE(vOrganisation[userId], vOrganisation[fullname],SELECTEDVALUE(Users[Manager Name])))

In order to only see the selected manager's employees/organization, I use this measure as a filter in my other measures, e.g. in the calculation of "All reports".

CALCULATE(calculate(DISTINCTCOUNT('Employees'[userId]) + 0),

FILTER('Employees',PATHCONTAINS(Employees[Employee-Manager Path], [Selected Manager])),

FILTER(Employees, Employees[userId] <> [Selected Manager]))

However, this only works when I am calculating and working with measures, which is only a small part of my dashboard, so I would therefore prefer to have Selected Manager work a page filter (filtering the entire data set based on the manager selected).

I was thinking a solution could be to create a (dynamic) calculated coloumn that based on the Selected Manager could return a "Yes" or "No" value, which I could then add as a page filter - something like this:

In Selected Manager Path = IF(PATHCONTAINS(Employees[Employee-Manager Path], [Selected Manager]), "Yes", "No")

But it isn't working 😌

I am fairly new to Power BI (as you can probably tell from my formatting), so I'm sure there is a good reason why I cannot just create this type of dynamic calculated coloumn. But can any of you help me figure out how to filter my entire dataset based on Selected Manager?

Thank you in advance 😊

[Anonymous]

HI @Anonymous,

In fact, calculated columns calculation results not able to dynamic changes based on slicer/filter selections.
They are stored in different data levels and calculated column/table is stored on data model tables. (it is the parent table of virtual source table that slicer/filter measured used)

As MFelix said, you can try to configure RLS filters based on username and multiple fields. (setting if statement to check both username and correspond mange level, then filter records based on management level and username group)

RLS with UserName() 
Regards,

Xiaoxin Sheng

[MFelix]

Hi @Anonymous ,

First of all let me apologize for the questions I'm asking but I need to understand the model.

To what I can see from your post you need to show their own data based on the employee-manager. If you are a manager you can see everything if you are a specific user you look for the user ID and you see those values correct?

Again maybe I'm reading this incorrectly but using Row Level Security  you can create filters so that you do not need to add filtering to your data because when you enter the Power BI the user id returns their data, and if you are a manager then you would see all of them. 

Regarding the last question about making the calculated column based on a measure that is not possible, columns are calculated at a row level context while measures are aggregations and can be calculated at different levels so when you pick up the value of a measure to a column it will always return the same value.

Just summarizing believe that you should:

• Create a Row Level Security to get data (forget about the SELECTEDMANAGER measure)
• Use the Parent Child for your Row Level Security.

Check the  post below with several explanations that can help to understand this complexety and suggest some ways:

https://www.sqlbi.com/articles/calculated-columns-and-measures-in-dax/

https://www.sqlbi.com/blog/alberto/2011/07/19/parent-child-hierarchies-in-tabular-with-denali/

https://docs.microsoft.com/en-us/power-bi/admin/service-admin-rls

[Anonymous]

Hi @MFelix

Thank you for looking into this for me, I really appreciate your time!

I think I probably didn't describe the issue clearly enough, hopefully I can explain a litte bit better what I need to do.

Right now, there is in fact RLS set up based on the principal user's ID and his/her employee-manager path, so the principal user only gets to see the data from employees reporting into him/her (both directly and indirectly). What I need is additional filtering of the dataset based on selected manager.

Let's say that the principal user is the CFO, Sarah. When Sarah enters the dashboard, she sees "her" data (her direct and indirect reports). This is fine and the RLS works perfectly. So far, so good. However, Sarah is interested in seeing the data of Lauren, one of the managers below her (i.e. Lauren is in Sarah's employee-manager path and Sarah therefore has access to this data via the RLS). For this purpose, I have inserted a slicer in the dashboard called "Manager Name" where Sarah can select Lauren's name, which means now I only want to see data that applies to Lauren. In such, when the Sarah selects Lauren's name, she expects to only see data from employees who are in Lauren's path (i.e. Laurens's direct and indirect reports).

Right now, selecting the manager's name in this slicer will convert the name into an ID in the measure "Selected Manager". I used IDs to create the employee-manager path, so this conversion is necessary in order to identify the employees who are in the selected manager's path. In my calculated fields (e.g. all reports, managers, new hires, avg. age, avg. tenure, gender, etc.), I have used "Selected Manager" to filter the data set according to the manager chosen in the slicer. This works out, no problem. My issue is that I am only able to apply this type of filter on the calculated fields/measures. The other fields that are pulled directly from my master data (e.g. organizational level, age distribution, etc.) are still only filtered based on the RLS and not on the selected manager.

My hope was that I, within the RLS, could somehow filter the entire dataset based on the slicer selection, which in this case is the selected manager's ID, e.g. by only including the rows where the selected manager's ID is present in the employee-manager path.

Does that make sense?

[MFelix]

Hi @Anonymous ,

Can you please share the way you have things setup between managers / and users. Do you have a single table with all the users? Do you have a column with Managers? How do you have the relationships setup?

Using the way the model is setup and the relationship you have this should be achievable without the need for measurements, or additional setup, tables should filter one another based on your selections.

[Anonymous]

Hi again,

Attached is my model - I hope this helps.

Most of my data is pulled from the "Employees" table, which is a combination of values from the other tables. The vOrganisation table is the one with all the users. The employee-manager path is calculated like this:

Employee-Manager Path = PATH(Employees[userId],'Employees'[manager_userid]).

The users sign into Power BI using their organization credentials, which I assume is what identifies their username, and their username is then converted into an id through the following measure:

Current Manager ID = LOOKUPVALUE(vOrganisation[userId],'vOrganisation'[userName], LOWER(USERPRINCIPALNAME()))

This measure is then used in RLS with the following definition:ModelRLS

PATHCONTAINS('Employees'[Employee-Manager Path],
MaxX(Filter('Employees', [userName]=USERPRINCIPALNAME())
,'Employees'[userName]))

Not sure if this is what you were looking for, but I hope it helped clarify things a bit!?