Many to Many relationship

[Anonymous]

Hi

I have a table Called OpportunitywithOpportunitylines (giving details about the opportunity) and I have anothe table called OpportunityTeam.

the table OpportunitywithOpportunitylines has the following fields : OpportunityId,Opportunity lineId, Currency, Amount, BusinessUnit,..... 

The OpportunityTeam table defines for each opportunity the members who will be part fo this opportunity with their roles :

the table has : OpportunityTeamId, OpportunityId, UserId, username, UserRole.

I am not using Opportunityteam table fo any calculation but only to slice and dice :  for instance I need to know the Total amount of opportunity or their count.

the relaionship between the two table is many to many

can you please guide me how I can make 1 to many relationshipor between the two tables and what kind of bridge I can use

thank you for your support

imbrg

[dax]

Hi @Anonymous , 

I am not clear about your requirement, if possible could you please inform me more detailed information(such as your expected output and your sample data (by OneDrive for Business))? Then I will help you more correctly.

Please do mask sensitive data before uploading.

Thanks for your understanding and support.
Best Regards,
Zoe Zhi

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

[Anonymous]

@Zoe Zhi

is the data I sent helpful tosupport my request

thank you in advance

regards

imbrg

[Anonymous]

Hi Zoe

I've attache the file (Link ) for your reference.

I've two tables

1  - Table OpportunitywithOppLine : where I have the opportunity (Header) with their opportunity lines (Detail) I'll sum the totalprice

2- OpportunityTeam : where I have all team members working on opportunities.

Opportunityteam has many to many relationship with  OpportunitywithOppLine

what I need is to be able to filter by team member and have the totalprice of opportunities where he is a member

hope this will help

thank you for your support

imbrg 

[dax]

Hi @Anonymous , 

I get the source, so if possible, could you please inform me your expected output? Then I will help you more correctly.

Please do mask sensitive data before uploading.

Thanks for your understanding and support.
Best Regards,
Zoe Zhi

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

[Anonymous]

I've two tables

I have created some measures from the table “OpportunitywithOppLine”

For instance TotalPipeline = sum(Totalprice)

 And I would like to be able to filter (slice) by user who are on the table

OpportunityTeam

Since the relationship between the two tables is many to many, I am not able to perform this action.

What I was havig in mind is to create a kind of bridge between the two tables and then be able to filter and have the filter propagate from OpportunityTeam

 To OpportunitywithOppLine

Please let me know if this is clear and you can support

Thank yoou Zoe

[dax]

Hi @Anonymous , 

Yes, I know, you want to use slicer to get corresponding value, but sometimes relationship can't achieve this directly, so we might need use measure to achieve this. If possible could you please inform me more detailed information(such as your expected output and your sample data (by OneDrive for Business), the link you provide can't be available currently)? Then I will help you more correctly.

Please do mask sensitive data before uploading.

Thanks for your understanding and support.
Best Regards,
Zoe Zhi