Forum Discussion
Filter data based on slicer selection
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.
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?
5 Replies
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
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?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.
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
