Forum Discussion
Filter a table based on a variable factor
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.
---
### ✅ 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 !!