Forum Discussion
Adding a Slicer to Many to Many Table
Hi everyone,
I have a campaign member table like this:
| Campaign ID | Name | Year | Status | Account ID |
| XXXXX | A | 2017 | New | GGGGGG |
| XXXXX | B | 2017 | 2 Years | YYYYYY |
| YYYYY | A | 2018 | New | GGGGGG |
I have another table called transaction:
| Campaign ID | Amount | Account ID | |
| XXXXX | $100 | GGGGGG | |
| XXXXX | $50 | YYYYYY | |
| YYYYY | $30 | GGGGGG | |
| ZZZZZ | $60 | GGGGGG |
I created a bridge table holding unique campaign IDs to avoid many to many relationships between campaign member and transaction table.
My viz looks like this and it is working:
| Year | Total Amount |
| 2017 | $150 |
| 2018 | $30 |
I want to add a slicer based on status, but the value remains the same. For example, if I add a slicer "Status - New", then I want to see the result of $100 for 2017 and $30 for 2018.
How do I accomplish this?
Here are some things I tried:
- Created a inactive relationship using account ID. Then, created a measure to add transaction amount using the inactive relationship.
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.
9 Replies
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
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.
Anonymous
1. Create one table having unique value of Campaign ID
Table3 = DISTINCT(UNION(DISTINCT(Table1[Campaign ID]),DISTINCT(Table2[Campaign ID])))2. Then create relationship between tables like below
3. You can create your visual like below
You can refer to my file as well.
Hi Anonymous ,
According to your description, I created the relationship:
Here are the steps you can follow：
1. Create measure.
Amount_1 = CALCULATE(SUM('transaction table'[Amount]), FILTER('transaction table','transaction table'[Account ID]="GGGGGG"&&'transaction table'[Campaign ID]=MAX('transaction table'[Campaign ID])))2. Result.
You can downloaded PBIX file from here.
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
