Using filter to exclude multiple matching rows

[golbarg]

Hello,

I'm trying to use filters to exclude data from a report, but can't get it just right.

Consider the following tables:

I am then creating a simple table visual in a report to display all names:

I then add the following filter:

The result is the same table as above. I understand that Cindy Harper (id 3) is still displayed because she matches a role that is selected, but I would like to have a result where the table would exclude that row from the visual. The filter "where role is not QA analyst" is misleading, because the resulting table does display someone with this role.

I'm wondering if there is a way to achieve this without having to duplicate data tables or using DAX, as it seems overkill for the goal I wish to achieve.

If there is no way to do this, I'll try looking into other solutions such as adding a column to the user table with concatenated role names, and applying a "does not contain" filter to it.

Thanks!

[v-henryk-mstf]

Hi @golbarg ,

According to your requirements, you can click on the table that needs to be filtered, and then uncheck Cindy Harper (id 3) in "FILTER on this visual" to eliminate this row in the visual object.

If the problem is still not resolved, please provide detailed error information and let me know immediately. Looking forward to your reply.

Best Regards,
Henry

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[golbarg]

Hi @v-henryk-mstf,

The data I provided is a sample, but my real data contains many rows. I also want filtering to be automated based on "roles" and not "people" as the "people" list changes over time.

Your solution would require manually selecting/removing people from the filter as the list changes through time, which is not viable.

So far, the best I've found as a workaround is to:

• concatenate roles in a new column in the "people" table
• add an Advanced Filter where the concatenated roles column does not contain "QA analyst"

Although this works fine, it is a little disappointing as data is being duplicated. It's not very problematic for my real data size, but could be at a bigger scale.

Thanks!

[samdthompson]

Hello, you need to have the role on the first table, given that its a many to one that your trying to achieve. All the data model knows is you have someone with ID 3 and it could be either one of those roles.