Forum Discussion
Filter a table based on a variable factor
I have an employee list with various information, important for this task are Employee ID, Grading, Salary. The goal is to make calculations and visuals for each job grade. Each employee can only have 1 ID and Grade but every Grade will have many employees in them. I am relatively fresh to Power BI and I tried really hard to get this working but failed.
What I have right now is a slicer for employee ID (purple) and measure (lookupvalues) to get the data from the employee list. Now I want to filter data from the employee list (lets say salary) with the corresponding variable (grade 9 here) of the slicer selection. I need the data on row level as I want to make calculations and feed statistical visuals only for this (variable) grade. Here is the code I have so far (Filtered List is the envisaged table, employees the source table).
This should be possible but not with standard tools, way above my capabilites. Can anybody help?
Thanks in advance!
So I finally made it thanks to your input though not directly but with a decent workaround.
I could not create a seperate virtual table as I was hoping for but the File from Anonymous showed me another way. I simply put 2 slicers on the same page. The slicer for the employee ID is connected to the individual values shown and the second slicer is connected to the visuals. I used the chiclet slicer and used the forced selection (on the grade for the employee) and adjusted the formating so it is not visible as a slicer and blends in.
7 Replies
---
### ✅ Step-by-Step Solution
#### 1. **Get the Selected Employee’s Grade**
You need a measure or variable that returns the Grade of the selected employee in the slicer:```DAX
SelectedGrade =
CALCULATE(
MAX(Employees[Grade]),
ALLSELECTED(Employees[EmployeeID])
)
```This measure captures the grade (e.g., 9) of the selected Employee ID.
---
#### 2. **Create a Filtered Table Using That Grade**
You can create a **calculated table** that returns only the rows from the `Employees` table that match that grade:
```DAX
FilteredByGrade =
VAR _SelectedGrade =
CALCULATE(
MAX(Employees[Grade]),
ALLSELECTED(Employees[EmployeeID])
)
RETURN
FILTER(
Employees,
Employees[Grade] = _SelectedGrade
)
```Or, more compactly as a **calculated table**:
```DAX
FilteredByGrade =
FILTER(
Employees,
Employees[Grade] =
CALCULATE(
MAX(Employees[Grade]),
ALLSELECTED(Employees[EmployeeID])
)
)
```You can now use this table in visuals (tables, charts, etc.) to show stats like total salary, average salary, etc., **only for the grade of the selected employee**.
---
#### 3. **Alternative: Visual-Level Filters Instead of Table**
If you don’t want to create a physical table, but just need a **measure** for use in visuals (e.g., average salary of others in the same grade):```DAX
AverageSalarySameGrade =
VAR _SelectedGrade =
CALCULATE(
MAX(Employees[Grade]),
ALLSELECTED(Employees[EmployeeID])
)
RETURN
CALCULATE(
AVERAGE(Employees[Salary]),
FILTER(Employees, Employees[Grade] = _SelectedGrade)
)
```---
### ✅ Visualization Tips
- Use a **card visual** to show average salary.
- Use a **table visual** with `FilteredByGrade` table to show employee details in same grade.
- Add slicers for `EmployeeID` (you already have this).---
Did I answer your question? Mark my post as a solution! Appreciate your Kudos !!
Thank you! The table is indeed being created but only for the maximum grade. It seems the first step is not properly working yet. When I put the calculation in a card, it shows me the correct results. Inside the table view however, it gives only blanks. Therefore the second step, gives me only the rows for the maxium grade I think. What am I missing?
Hi ThomasBu ,
Thank you for reaching out to Microsoft Fabric Community Forum.
johnbasha33 Thank you for your quick response.
ThomasBu could you please share the expected result or a sample output you're aiming for? That would help a lot in providing a more accurate solution.
👉 Please make sure not to include any sensitive or confidential data in the sample.
Also, if the issue has already been resolved, we'd really appreciate it if you could share the steps that worked for you ,it might help others facing the same problem. And if a reply helped solve it, please consider marking it as a solution so it benefits the wider community.
Regards,
B Manikanteswara Reddy
Thanks for reaching out. Please see below what I am aiming for. Per selected slicer it should lookup the corresponding grade in the employee list (already working). Then a new table should be created filtering all employees within this grade from the employee list. Based on this (dynamic) table, I want to create statistical charts based on all these filtered value. Data needs to automatically refresh once the slicer is chosen/changed. Hope that helps If anything was unclear (data is fictious for testing of course).
