Many to Many Relationship

[EMGI]

Hey everybody,

i need your help. Let me explain my problem:

I have 2 tables which will be updated/imported every month with a new csv-File.

In the table "calculated table" i have different, multiple OfferIDs (ID of the product) with Quantities of the licence for each customer.

In the table "Pricelist" i have for each month AND for each OfferIDs a price.

Both tables will be updated every month with new data.

For example:

calculated table

CUSTOMER      OFFER ID        QUANTITY      STARTDATE         ENDDATE          

Mayer              123a               20                  15.01.2017        31.01.2017

Mayer              123a               15                  01.02.2017        14.02.2017

Pricelist

OFFER ID         VALIDE FROM       PRICE

123a                01.01.2017           0,68

123a                01.02.2017           0,50

In my opinion, i have many-to-many relationship because in the calculated table i have multiple times the OfferID and in the Pricelist table too.

At the end i want the price for each customer for each OfferID.

Thanks a lot in advance.

Best regards EMGI

[ZinouBakhouche]

I have another issue related to Many to Many relationship, we were moving from one forecasting tool (F1) to another one (F2) the two files have:

- Same Report line key" (high Level) but different "Account Key", the issue is that Report lines are at higher level means one report line can have multiple Account Key in both systems.

- Another issue is that one Account Key in (F1) can be linked to multiple lines in (F2) via reportline and vis versa (one Account Key in (F2) can be linked two multiple lines in (F1) via reportline). means that reportline cannot be used as a unique value to create a relationship nor account levels as they have many to many relationship.

- I created a bridge but with Reportline only, where I'm relating both tools together tight to report line, but this will not help me to check the F1 and F2 variances at account level (I got the same number everywhere.

is there anyDAX formula which could help me solve this issue?
thanks in advance.

[miltenburger]

Hi @EMGI,

First thing i'm thinking of to tackle this problem:

Make a calculated column in your calculated table where you put the price.

So based on the start date in calculated table you create a column where you put your price for the specific customer for a specific orderId.

You get me?

[EMGI]

Thanks for fast reply, but i didn't get you.

How can I set up a calculated column with the price if the relationship don't exists???

[miltenburger]

Ah sorry,

I thought you already had the relationship.

Then you have to create a 'link table'. What you probably need in this link table:

- OrderId

- PartnerId

- Startdate

All defined as primary key. Then link to your other tables. Then your many-to-many relationship becomes one-to-many and one-to-many

[EMGI]

My problem is how to create a link table with the values of the other two tables?

Which columns i need in the link table to get going on with the calculation?

I think the table has to be dynamically updated every time a new OfferID and a new price is inserted, or am i wrong?

[Anonymous]

HI @EMGI,

You can try to create calculated table with 'Union' and 'DISTINCT' functions to merge columns and get unique id.

Sample formula:

Link Table =
DISTINCT (
    UNION ( VALUES ( calculated[OFFER ID] ), VALUES ( Pricelist[OFFER ID] ) )
)

Regards,

Xiaoxin Sheng

[EMGI]

@Anonymousthank you for reply.

I created the linked table according to your explanation above.

I create a table with OfferName of the calculated table and the List Price of Pricelist. If i choose "don't summarize" i get the error message "can't display the visual".

What do i have to do to create a visual with the correct list price for each OfferName( Product) ?

Hope you can help me, @Anonymous

[Anonymous]

Hi @EMGI,

Have you drag the product id(from link table) to table visual? Current power bi seems not support to analysis mapping with multi-columns, you need to drag summary column to help power bi correct recognize records mapping.

Regards,

Xiaoxin Sheng

[EMGI · Accepted solution]

Hi everbody,

got the solution. First problem was to choose the wrong "offerID"-column to get a relationship.

I created a unique ID in the Pricelist concatenate with OfferID + Month(ValidFromDate) + Year(ValidFromDate).

Thanks for replies and help.