Forum Discussion
Adding a Slicer to Many to Many Table
Thanks Tim. I am still verifying numbers with my report, but I think I found a solution. I created a conc column (campaign ID & account ID) for both my campaign member table and transaction table. I also created a bridge table with unique conc from both tables. Then, I linked relationships based on conc, not campaign id. It works in my example table, but hoping this solves my issue. I appreciate your help.
Hi Cerami,
I assume you will have to enable filtering in both directions for the relationship between your campaign member table and the bridge table. This will allow the status filter in the campaign member table to carry on to the bridge table, which will subsequently filter the transactions table. While the relationship filter option is set to "one" sided, the filter on status will not be able to carry on to the other tables.
You can find the filter direction option in the model view by double clicking the relationship between the two tables.
Hope that solves it!
Regards,
Tim
Hi Tim,
Thanks for your quick response. I tried it, but it still doesn't work.
Total amount still shows $150, when I add the slicer of "New".
ah apologies I didn't see the full context yet. I see now in your campaign table that one campaign can have multiple records with different statusses.
In the current setup it would not be possible to do this since the transaction table aggregates all values to campaign, not campaign+status. so as long as your status slicer returns a certain campaign, the filter will merely pass along that everything from that campaign should be filtered. to filter the transactions also based on status would require the data in the transaction table to also be collected on that level. Right now there is no interpretation of which proportion of the amount in the transaction table belongs to which status of the selected campaign.
Thanks Tim. I am still verifying numbers with my report, but I think I found a solution. I created a conc column (campaign ID & account ID) for both my campaign member table and transaction table. I also created a bridge table with unique conc from both tables. Then, I linked relationships based on conc, not campaign id. It works in my example table, but hoping this solves my issue. I appreciate your help.