Forum Discussion

EMGI's avatar
EMGI
Frequent Visitor
8 years ago
Solved

Many to Many Relationship

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

Need Help
  • EMGI's avatar
    EMGI
    8 years ago

    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.

9 Replies

  • miltenburger's avatar
    miltenburger
    Helper V
    8 years ago

    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's avatar
      EMGI
      Frequent Visitor
      8 years ago

      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's avatar
        miltenburger
        Helper V
        8 years ago

        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

  • ZinouBakhouche's avatar
    ZinouBakhouche
    New Member
    8 years ago

    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.