Re: Relation between two columns is not woking

saracaballero · ‎08-08-2019

Dear all,

I am currently developing a pipeline for the sales team. I want to analyse number of leads, opportunities and sales orders for a point in time.

Leads and opportunities are in the same table, which is called crm_lead, whilst sales orders are in a tables named sale_order.

I want to track those sales orders that came from opportunities. In my database, I have two "types" of relation between opportunities and sales orders:

1. One opportunity transforms into one sale order.

2. One opportunity transforms into multiple sales orders.

I have a column in sale_order named origin, which gives me the opportunity id in the following way:

I managed to separate the id number from the opportunity and now I have a new colunm, named id_opportunity, with only the id number.

Afterwards, I created a relation between sale_order [id_opportunity] and crm_lead[id], in order for PowerBi to track those sales orders that came from an opportunity.

The problem is that when I create this relation, those opportunities that have multiple sales orders (type two) disappear, and are not counted correctly as sales orders.

Here find attached an example:

I had two sales orders the last 1th July 2019. One of them came from the opportunity with id=2295, whilst the other came from the opportunity with id=2641.

The opportunity with id=2295 has another two sales orders attached, all of them in different dates (corresponds to type two)

On the other hand, the second sale order, with opportunity id = 2641, has only one sale order attached.

When I enable the relation following relation: sale_order[id_opportunities] and crm_lead[id], and I specify it as many to one, the following happens:

Could you help me fix this problem?

If anything is not clear, please feel free to ask whatever question you need.

Thank you very much in advance,

Sara.

Mariusz · ‎08-09-2019

Hi @saracaballero

Are [id_opportunity] and crm_lead[id] data type Whole Number?

Best Regards,
Mariusz

Please feel free to connect with me.

saracaballero · ‎08-11-2019

HI @Mariusz,

Both columns in crm_lead (id and id_opportunity) are identified as whole numbers.

Kind regards,

Sara.

Anonymous · ‎08-08-2019

Is it true that each opportunity is only ever tied to one and only one order but an order can have multiple opportunities? Or is it that it's a M2M relationship between OrderId and OpportunityId?

If it's M2M, you'll need a bridge table or a weak M2M relationship with some kind of wisely chosen filtering. I would not recommend cross-filtering because it's dangerous.

Best
Darek

saracaballero · ‎08-09-2019

Hi @Anonymous ,

Thank you for responding to the question.

One opportunity can be tied to:

- Only one sales order.

- Multiple sales orders.

A sale order can be tied only to one opportunity, but multiple sales orders can be tied to that same opportunity. Thus, in my opinion, the appropiate relations between both variables should be one to many.

I have looked up for a bridge table, but I have not found any.

If you have any other suggestions, please let me know.

Best regards,

Sara.

Anonymous · ‎08-09-2019

To know what's going on we have to see the measure(s).

Please confirm that there is a one-to-many relationship defined between crm_lead and sale_orders. To which table is the table of dates related? Because... you do have a proper Date table in the model, right?

Best
Darek

saracaballero · ‎08-12-2019

Hi @Anonymous,

The relationhip between crm_lead and sale_order is one to many. For now, I am using the dates provided in the same table, that is, create_date, to filter sales orders. Later on, when I make sure that everything works, I will create a Date table.

If you want a sample of the data, please go to:

sale_order: https://drive.google.com/open?id=1_j_OTx3Bmq-0HVIecpwDFWScDBWsKZVK

cm_lead: https://drive.google.com/open?id=1ZROMqLmjw5mQN8Auf_VXPAug30__8XYr

Thank you,

Sara.

Anonymous · ‎08-12-2019

I will have a look but... your thinking is completely wrong. The very first thing to do when building a model is create a proper Date table. This is THE VERY FIRST thing to do, not the last one.

Best
Darek

saracaballero · ‎08-12-2019

Hi @Anonymous ,

Ok, thanks!

The thing is I want to create a calendar and connect all the dates that I am interested in filtering. However, for crm_lead, I want to columns of dates to be attached to the same Date table, which I think it is not possible. For this reason, I am waiting to see if this works to take the next step.

I am a Junior who just graduated, and this is the first time I am working with these types of programs, so I apologise if my procedure is not the correct one, because I am not an expert. Nevertheless, I am very willing to learn.

Best regards,

Sara.