Display Accounts when no Opportunities linked to Account have field set to X

[RM4D]

Hi, community - 

I am new to Power BI and am working on building my first dashboard with information I've pulled in from Salesforce via a Salesforce Report.

I would like to display a table that lists only the accounts where there is not at least one opportunity linked to an account where a specific opportunity field is not set to X. There is an action that needs to be taken for these accounts. If the account has an opportunity with the specific field set to X, I don't want them to display in the table. Most accounts will have many opportunities and the field I'm focused on will be set to Y, Z, etc. and unfortunately only sometimes X!

In the table below I would want Account 1 to be returned/displayed and would not want Account 2 to be returned in the formula/displayed in the table. Is there a way to accommodate this? Thank you!

Account	Opportunity	Special Field
1	A	Y
1	B	Z
1	C
2	D	Y
2	E	Z
2	F	X

[RM4D]

Very cool, thank you @vicky_ ! I am playing around with adding the logic you outlined in your response, but I can't figure out how to apply it to the visual I have with the four columns as outlined above. I read through https://learn.microsoft.com/en-us/power-bi/create-reports/power-bi-report-add-filter?tabs=powerbi-de... and it only explains how to add the simple formatting I have used. How do you add the more complex logic to as a visual-level filter? Thank you so much for your help!

[vicky_]

Hello, 
I'm not sure I completely understand your problem, but I think the microsoft page you linked is a good place to start. Any complex logic that you do will need to happen in the measure, rather than the filter.

If I understood the original problem to hide accounts where there is a field = X, then you can just drag the "Account Contains X" measure to the "Filters on this visual" pane and set it to "Show items when the value is not 1".  

[vicky_]

Account Contains X = 
var fields = CALCULATETABLE('Table', ALLEXCEPT('Table', 'Table'[Account]))
return IF(CONTAINS(fields, 'Table'[Field], "X"), 1)

Here's something I came up with - that you can use as a visual-level filter to hide Account 2. Hope it helps.