Automatic Filtering for Slicers

[SHA03]

Hi Everyone,

I'm working on a calculator in power bi that calculates salary increases for selected employee number (slicer), so when employee selected, the cards will shows results of calculations based on that employee.

My issue is that i have visuals showing the salaries of employees on the same level or same department, so these visuals cannot have an interaction with the employee number slicer, to quickly solve this issue i disabled the interaction between the slicers & visuals and created 2 new slicers for department & level, however the user must check the boxes of the slicers so it will refelct the data on the visulas. This quick solution doesnt enhance the user experience in any way, as i want the user to use only one slicer (employee number slicer).

in these visuals im using a column not a measure to showcase the values to i couldnt use the function REMOVEFILTERS and using visual level measures didnt work either.

If you have any idea how to make the Dept & level slicers filter automatically or work on any other solution from a different angle please let me know!

Thnaks in advance.

[v-venuppu]

Hi @SHA03 ,

As we didn't heard from you, we will proceed with closing this thread.

If you need further assistance in the future, feel free to start a new thread in the Microsoft Fabric Community Forum. We will be happy to support you there.

Thank you.

[v-venuppu]

Hi @SHA03 ,

Thank you for your Patience.

I somehow figured it out to get below output:

If I select employee #1003 (Charlie – Marketing – Level1), then the visual should show Charlie and Frank's Salaries (since both are in Marketing and at Level 1).

Please go through the attached pbix file.

1. Created a disconnected table with all employees

2.Created "ShowPeerEmployees"--Measure.

3.Add the ShowPeerEmployees measure as a visual-level filter, set to = 1.

If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it! 

Thank you.

[v-venuppu]

Hi @SHA03 ,

Thank you for the explanation.

Sorry for the delay in response.

Working on the solution to solve the issue, will post the solution as soon as the workarounds will help me to solve it.

Thank you.

[v-venuppu]

Hi @SHA03 ,
Thank you for the Response..!!
Not sure if I understood correctly for the below point mentioned by you:

"So in your example, if i filtered with an Employee No of someone in Marketing and Level 1. I'll be getting the data of all employees in Marketing and on Level 1, not just the one employee."

I was able to see the data for one employee when i filtered with an Employee No of someone in Marketing and Level 1.

Please find the screenshot for your reference:

If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it!

Thank you.

[SHA03]

Hello @v-venuppu 

The point of the visuals is to showcase Internal Equity. So, when using the slicer (e.g., employee number), the data for the selected employee should reflect across the dashboard.

However, for the visuals that show Internal Equity, I need them to display all employees who are on the same level and in the same department as the selected employee. This helps highlight any pay variations.

This means I’m stopping the interaction between the employee number slicer and these specific visuals.

So, based on your data:
If I select employee #1003 (Charlie – Marketing – Level1), then the visual should show Charlie and Frank's Salaries (since both are in Marketing and at Level 1).

This way, I can view the internal pay equity for Marketing at Level 1.

Hope I explained it clearly now.

[v-venuppu]

Hi @SHA03 ,

Thank you for the Response..!!

I have attached a PBIX file with sample data to replicate the scenario. I hope it helps.

If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it! 

Thank you.

[SHA03]

Hello @v-venuppu 

Great concept. 

However, as i might have mentioed in my post., I need the visuals to filter by the level and Dept not by Employee No.

So in your example, if i filtered with an Employee No of someone in Marketing and Level 1. I'll be getting the data of all employees in Marketing and on Level 1, not just the one employee.

I hope this makes sense to you.

[v-venuppu]

Hi @SHA03 ,

I hope this information is helpful. Please let me know if you have any further questions or if you'd like to discuss this further. If this answers your question, please accept it as a solution and give it a 'Kudos' so other community members with similar problems can find a solution faster.

Thank you.

[SHA03]

Hi @v-venuppu ,

Appreciate your response.

I tried your method but unfortuntely it didnt work for me. instead of filtering, the data on the visual disappeard. So im still trying to find a way to work this out.

Again, really appreciate your time!

[v-venuppu]

Hi @SHA03 ,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.

Thank you.

[v-venuppu]

Hi @SHA03 ,

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[v-venuppu]

Hi @SHA03 ,

Thank you for reaching out to Microsoft Fabric Community.

Thank you @FarhanJeelani for the prompt response.

Here are few steps to solve the issue:

1.Create a Disconnected Employee Table
Go to Modeling > New Table 

EmployeeList = DISTINCT(EmployeeTable[Employee Number])

This table will act as your slicer source.

2.Create a slicer from this new EmployeeList table
Use EmployeeList[Employee Number] in your slicer.

3.Create measures for selected department and level
These will pull values based on the slicer:

Selected_Dept =
VAR _Emp = SELECTEDVALUE(EmployeeList[Employee Number])
RETURN CALCULATE(
MAX(EmployeeTable[Department]),
FILTER(EmployeeTable, EmployeeTable[Employee Number] = _Emp)
)

Selected_Level =
VAR _Emp = SELECTEDVALUE(EmployeeList[Employee Number])
RETURN CALCULATE(
MAX(EmployeeTable[Level]),
FILTER(EmployeeTable, EmployeeTable[Employee Number] = _Emp)
)

4.Add visual-level filters to your “Same Dept” and “Same Level” visuals
For “Same Department” visuals, add a filter:

EmployeeTable[Department] = [Selected_Dept]

For “Same Level” visuals:

EmployeeTable[Level] = [Selected_Level]

This works because the disconnected slicer doesn't interfere with the visuals, but your measures still pull the needed values for filtering.

If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it! 

Thank you.

[FarhanJeelani]

HI @SHA03 ,

You can achieve this using SELECTEDVALUE and LOOKUPVALUE in DAX to dynamically filter the department and level based on the selected employee.

Solution Approach
Create Measures for Department & Level Selection

Use the SELECTEDVALUE function to get the department and level of the selected employee.
Use LOOKUPVALUE to extract the department and level from the employee table.
Apply These Measures as Filters

Modify your existing visuals for "Same Department" and "Same Level" employees using these measures as filters.

Step-by-Step Implementation
1. Create a Measure for the Department of the Selected Employee

Selected_Dept =
VAR _SelectedEmp = SELECTEDVALUE(EmployeeTable[Employee Number])
RETURN LOOKUPVALUE(EmployeeTable[Department], EmployeeTable[Employee Number], _SelectedEmp)

2. Create a Measure for the Level of the Selected Employee

Selected_Level =
VAR _SelectedEmp = SELECTEDVALUE(EmployeeTable[Employee Number])
RETURN LOOKUPVALUE(EmployeeTable[Level], EmployeeTable[Employee Number], _SelectedEmp)

3. Apply These Measures as Visual-Level Filters
In the visuals showing employees from the same department, set a visual-level filter where:
EmployeeTable[Department] = [Selected_Dept]

In the visuals showing employees from the same level, set a visual-level filter where:
EmployeeTable[Level] = [Selected_Level]

Expected Outcome
When a user selects an employee, the department and level slicers are no longer needed.
The "Same Department" and "Same Level" visuals will automatically filter based on the employee’s department and level.

Please mark this post as solution if it helps you. Appreciate Kudos.

[SHA03]

Hello Farhan,

Appreciate your reply however your solution doesn't work. When using SelectedValue as a visual level measure it wouldnt show any data, as there's no interaction between the employee number slicer and the visuals so the SelectedValue based on employee number naturally wont appear.

I also tried keeping the interaction between the main slicer and visuals but that didnt work as well.

besides this, i've already tried creating a calculated column (and a calculated table) to use in the measures instead of the main 'employee number' column but this solution didnt work either.

Let me know if you have any other ideas!