Problem Statement: Power BI Table Interaction Issue with Account and Opportunity table Visuals

[Anonymous]

Objective:
The goal is to create a Power BI report that displays two separate tables: one for Accounts and one for Opportunities.
These tables are connected via a one-to-many relationship, with Accounts on the "one" side and Opportunities on the "many" side.

Current Behavior:
When a user clicks on an Account in the Account Table, the Opportunity Table correctly filters to show only the Opportunities related to the selected Account.
However, when a user clicks on an Opportunity in the Opportunity Table, the Account Table filters to show the related Account,
but the Opportunity Table resets and shows all Opportunities associated with the selected Account, rather than displaying just the selected Opportunity.      

Desired Behavior:
When a user clicks on an Opportunity in the Opportunity Table, the Opportunity Table should display only the selected Opportunity, without resetting the entire Opportunity Table to show all related Opportunities for the Account.
When a user clicks on an Account, the Opportunity Table should show all Opportunities related to the selected Account.
This allows the user to easily drill into an Opportunity while still maintaining the ability to see all Opportunities for an Account.

[Anonymous · Accepted solution]

Hi @Anonymous ,

Whether the advice given by rajendraongole1 has solved your confusion, if the problem has been solved you can mark the reply for the standard answer to help the other members find it more quickly. If not, please point it out.

Best Regards,

Neeko Tang

[rajendraongole1]

Hi @Anonymous - you need to adjust how interactions between the two tables (Accounts and Opportunities) are handled.

you can select the Opportunity Table visual in your Power BI report.Go to the Format Pane, then select Edit Interactions from the ribbon.For the Opportunity Table, ensure that its filtering interaction with itself is set to "None" (represented by a circle with a diagonal line). This prevents the Opportunity Table from resetting when an Opportunity is selected.Create a calculated column or measure in the Opportunity table to filter based on the selected Opportunity.Add this measure as a filter on the Opportunity Table visual.

SelectedOpportunity =
IF(
ISFILTERED(Opportunity[OpportunityID]),
Opportunity[OpportunityID],
BLANK()
)

Apply this measure as a visual filter to the Opportunity Table, ensuring it only shows rows when the OpportunityID matches the selected one.
If the user clicks an Account, ensure the Opportunity Table is still filtered based on the related Account. This should happen automatically due to the one-to-many relationship between Accounts and Opportunities.
No additional steps are needed here unless custom filtering logic is required.Click on an Account in the Account Table and verify that the Opportunity Table displays all Opportunities for that Account.
Click on an Opportunity in the Opportunity Table and verify that it shows only the selected Opportunity while the Account Table updates to display the corresponding Account.

Hope the above steps., helps when clicking on an Account filters the Opportunity Table, and clicking on an Opportunity does not reset the table