Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
RM4D
New Member

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

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!

 

AccountOpportunitySpecial Field
1AY
1BZ
1C 
2DY
2EZ
2FX
3 REPLIES 3
RM4D
New Member

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!

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_
Super User
Super User

vicky__0-1689030158958.png

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.

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors