Filter a table based on a variable factor

[ThomasBu]

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!

[ThomasBu]

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?

[v-bmanikante]

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

[ThomasBu]

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).

[v-bmanikante]

Hello @ThomasBu ,

Thank you for following up.

Kindly review the output in the attached PBIX file and let us know if everything looks good or if any modifications are needed.

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!

Regards,

B Manikanteswara Reddy

[ThomasBu]

Thanks for your effort, highly appreciated! It is not yet working unfortunately. The page 2 selects only the highest grade. It seems to me that the allselect function doens't work with the slicer. I adjusted the File slightly and put the Employee ID in the slicer and a card with the grade per valuelookup. This grade should filter then the table on the second page but I can't get this working so far.

Here is the link to the one drive or a screenshot if you don't want to open the ink.

Employee edited 

[ThomasBu · Accepted solution]

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 @v-bmanikante 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.

[johnbasha33]

@ThomasBu 

---

### ✅ 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 !!