cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Budfudder Member
Member

Linking Two Tables and Filtering Result

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

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Linking Two Tables and Filtering Result

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
)

1.PNG

 

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

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
1 REPLY 1
Community Support Team
Community Support Team

Re: Linking Two Tables and Filtering Result

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
)

1.PNG

 

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

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.