Linking Two Tables and Filtering Result

[Budfudder]

In the attached example, the EMPLOYEES table and the SALES table are linked by the EMPLOYEEID column in both. Both columns also have the EMPLOYEENAME column.

But when I drag the EMPLOYEENAME from both tables to use as a slicer, the EMPLOYEEID slicer from the EMPLOYEE table shows all (4) of the employees; the EMPLOYEEID slicer from the SALES table shows only 3; the 3 who actually appear in the SALES table.

So why, when I use the list from the EMPLOYEE table, does it not trim the list down to the employees who are present in the SALES table?

And how can I make that list (from the EMPLOYEE table) show only those employees in the SALES table?

Here's the example: Example.pbix

[v-yulgu-msft · Accepted solution]

Hi @Budfudder ,

---

But when I drag the EMPLOYEENAME from both tables to use as a slicer, the EMPLOYEEID slicer from the EMPLOYEE table shows all (4) of the employees; the EMPLOYEEID slicer from the SALES table shows only 3; the 3 who actually appear in the SALES table.

So why, when I use the list from the EMPLOYEE table, does it not trim the list down to the employees who are present in the SALES table?

---

This is by design. The slicer would list all available unique items of that field you choosed from data table. It won't be filtered by any relationship, unless you apply filters later. As a workaround, you could create a calculated column in EMPLOYEE table, and add this column into "Page level filters".

Index =
IF (
    'EMPLOYEES'[EMPLOYEENAME]
        = LOOKUPVALUE (
            SALES[EMPLOYEENAME],
            'EMPLOYEES'[EMPLOYEENAME], 'EMPLOYEES'[EMPLOYEENAME]
        ),
    1,
    0
)

However, that case, all visuals on report page would be affected by the "Page level filters". In your scenario, I perfer to directly drag [EMPLOYEENAME] from SALES table into slicer.

Regards,

Yuliana Gu