Adding a Slicer to Many to Many Table

[Anonymous]

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.

calculate(sum(Transaction[Amount]),USERELATIONSHIP(Transaction[AccountId],'Campaign Member'[AccountId]))

This won't work as it adds all transaction made by account. For example, it returns $190 for account GGGGG.

- Merged 2 tables. This won't work either as there are 150M rows and Power BI gives me an error saying the there is not enough memory.

[v-yangliu-msft]

Hi  @Anonymous  ,

Has your problem been solved? If it is solved, you can mark the solved answer as a mark

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.

[v-yangliu-msft]

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.

[Anonymous]

Hi, I have 1M different account ID, so I am hoping I can find a way to filter data automatically. Thanks for your response though.

[negi007]

@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.

[Anonymous]

Hi, thanks for your response. My ideal result is $100 for campaign XXXX when "new" is filtered.

[timg]

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

[Anonymous]

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".

[timg]

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 · Accepted solution]

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.