Rows exclusion based on 2 columns

[Heleo]

Hi,

I'm currently trying to filter a table to exclude rows based on the value of one column, but I also want that if one value of another column was associated with it then exclude it completely as well. 

Example of what I have:

Company Name	Option
ELE	Bank
ELE	Computer
STE	TV
STE	Computer
JAQ	Bank
THO	TV

Now, I want to remove all the companies that have the Option "Bank". But, if I just simply filter my table to exclude the Option value "Bank", it removes just the rows with this value, like this:

Company Name	Option
ELE	Computer
STE	TV
STE	Computer
THO	TV

However, my company name "ELE" which has 2 options "Bank" and "Computer" still appears with the row "Computer", and I don't want it. Since it had the value "Bank" associated, I want all the rows with this company name to be excluded.

This is the result I'd like with my filter:

Company Name	Option
STE	TV
STE	Computer
THO	TV

How could I manage to achieve this? 

I thought of filtering first on the companies that had the value "Bank" to retrieve these values, then excluding the rows that had these company names. But I don't really know how to start. 

Thank you for your help!

[mahenkj2]

Hi @Heleo 

Same way you explained, you need to filter two times. You can try in power query:

Hope it helps.

[Heleo]

Thank you for your answer, the thing is that I don't want to have to write all the company names myself to exclude them. I would like them to be retrieved and excluded automatically. In my example, I only have one value but in real life, I have thousands. 🙂 

[mahenkj2]

Hi @Heleo ,

To work efficiently in power bi or any such reporting tool, I would recommend using best practices. One of the key thing related to your application is, using star schema or dimension and fact tables. The attributes shown in the sample data you showed is kind of dimensions and should be used as a slicer or filter. I am sure in your real data, you would have several other columns, which will carry mostly numeric values or some other attributes. That should work as fact tables and then you create relationships between dimension and fact tables.

If you are a new user, it might be difficult for you to quickly grasp what I said above and my expalnation not might be so succint, but it should give you a direction to start with.

Just look at some snapshots I wish you could check:

Then your report might be like this:

There are further to this to learn about design considerations for star schema and I dont want to overwhelm for this question.

Hope it helps.