Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anonymous
Not applicable

Adding a Slicer to Many to Many Table

Hi everyone,

 

I have a campaign member table like this:

Campaign IDNameYearStatusAccount ID
XXXXXA2017NewGGGGGG
XXXXXB20172 YearsYYYYYY
YYYYYA2018NewGGGGGG

 

I have another table called transaction:

Campaign IDAmount 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:

YearTotal 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.
1 ACCEPTED SOLUTION
Anonymous
Not applicable

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.

View solution in original post

9 REPLIES 9
v-yangliu-msft
Community Support
Community Support

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
Community Support
Community Support

Hi  @Anonymous  ,

According to your description, I created the relationship:

v-yangliu-msft_0-1612400152769.png

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.

v-yangliu-msft_1-1612400152771.png

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
Not applicable

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
Community Champion
Community Champion

@Anonymous 

1. Create one table having unique value of Campaign ID

Table3 = DISTINCT(UNION(DISTINCT(Table1[Campaign ID]),DISTINCT(Table2[Campaign ID])))
negi007_6-1612281487923.png
 

2. Then create relationship between tables like below

negi007_5-1612281476893.png

3. You can create your visual like below

negi007_4-1612281463006.png

 You can refer to my file as well.

 

 




Did I answer your question? Mark my post as a solution!
Appreciate your Kudos



Proud to be a Super User!


Follow me on linkedin

Anonymous
Not applicable

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

timg
Solution Sage
Solution Sage

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.

1.PNG

Hope that solves it!

 

Regards,

 

Tim





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Anonymous
Not applicable

Hi Tim,

 

Thanks for your quick response. I tried it, but it still doesn't work.

cerami_0202_0-1612281025507.png

Total amount still shows $150, when I add the slicer of "New".

cerami_0202_1-1612281073160.png

 

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.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Anonymous
Not applicable

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.

Helpful resources

Announcements
September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Top Solution Authors