Select through multiple tables

[cphite]

I have three tables - Campaigns, Leads, and Opportunities.  

Campaign can have one or more Leads, and each Lead can have one or more Opportunities.

What I want to do is have the Campaigns table populate when you open the report.  When you click on a Campaign, I want it to populate the Leads table; and when you click on a Lead, I want it to populate the Opportunities table.  If I click on an Opportunity, I want it to do nothing.

What is happening instead:  All three tables populate.  If I click on a Campaign, the Leads populate - which is good - but when I click on a Lead, it actually *unselects* that Lead and repopulates and repopulates the all three tables..  Likewise with the Opportunities table, clicking on an Opportunity actually unselects that Opportunity and repopulates the all three tables.

[v-junyant-msft · Accepted solution]

Hi @cphite ,

Thanks for amitchandak's reply!
And @cphite , This sounds like a problem with the interaction between visual objects. You can refer to the following steps:
Click the Leads table, click Format > Edit interactions:

Cancel the interaction between the Leads table and the Campaigns table:

Then click the Opportunities table, and cancel the interaction between the Opportunities table, the Campaigns table and the Leads table:

And the final output:
Click Campaigns:

Click on the Leads table while holding down the “Ctrl” key :

If I only click Leads, The Campaigns table will not be affected:

If I only click Opportunities, nothing will be affected:

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

[amitchandak]

@cphite , You need three facts and each fact all attribute from table above

Campaigns - Campaigns

Lead - Campaigns+ Lead 

and Opportunities -- Campaigns+ Lead  +Opportunities 

Campaigns and Lead will individually will become dimension along with date. and will join with all three facts.

All common attribute you want drill will come from dimension.