Forum Discussion

Anmolgan's avatar
Anmolgan
Post Prodigy
6 years ago
Solved

Many to Many Relationship Not Working?

Hi, I am trying to establish many to many relationship using Customer Groups, I have 1 Master Query and source of another query is spreadsheet, now in my spreadsheet I have 4 columns on which only 3 columns are userful, Customer Group, Month/Year, Royalty, now I can only form Many To Many realtionships between these 2 queries, Now in my report I am using 3 filters Sales Group, Customer Group, Year/Month Wise, all of which I am taking from Master Query itself, in my table I am using Customer Group, sales volume KL and Royalty, for each of the royalties for 1 customer that are coming for different months are getting repeated in one filter itself.

 

Below are my powerbi and spreadsheet details:

 

https://ifitech-my.sharepoint.com/:u:/g/personal/anmol_ganju_ifi_tech/EXhezhJdvXhGs_a1Q_aoV5sBXMlnaURewfu__BKSBVPp7A?e=cJaYhg

https://ifitech-my.sharepoint.com/:x:/g/personal/anmol_ganju_ifi_tech/EbG9yQi0NQFCvpcZS1qtmlcB4uAoe9Z-XaIexOIvGoUu4w?e=AOVAVV

Need Help
Show and Tell
  • v-lid-msft's avatar
    v-lid-msft
    6 years ago

    Hi Anmolgan ,

     

    First of all, if your shared pbix file contain any confidential information or come from real data, we suggest you to delete the shared link as soon as possible.

     

    The Many to Many Relationship is works with the Group Name Column, for example, the following slicer will filter two groups:

     

     

    Then in the Month Table , thoes two group are releated with following sub table.

     

     

    So it will show the data of another month because it does not have a relationship between date. we can try to use "Year Month" column from month table as slicer to solve this.

     


    Best regards,

     

  • v-lid-msft's avatar
    v-lid-msft
    6 years ago

    Hi Anmolgan ,

     

    Sorry for late reply, we can create a measure to calculate the total value of Sales Volumn and only use the month column in Month Wise Royalty table as the slicer:

     

    Sales Volume KL Measure =
CALCULATE (
    SUM ( ZSD_MAT_MARGIN_Q003[Sales Volume KL] ),
    FILTER (
        'ZSD_MAT_MARGIN_Q003',
        'ZSD_MAT_MARGIN_Q003'[Calendar Year/Month.Calendar Year/Month Level 01]
            IN SELECTCOLUMNS (
                FILTERS ( 'Month Wise Royalty'[Month/Year] ),
                "MonthYear", UPPER ( [Month/Year] )
            )
    )
)

     

     


    Best regards,

     

11 Replies

  • v-lid-msft's avatar
    v-lid-msft
    Community Support
    6 years ago

    Hi Anmolgan ,

     

    First of all, if your shared pbix file contain any confidential information or come from real data, we suggest you to delete the shared link as soon as possible.

     

    The Many to Many Relationship is works with the Group Name Column, for example, the following slicer will filter two groups:

     

     

    Then in the Month Table , thoes two group are releated with following sub table.

     

     

    So it will show the data of another month because it does not have a relationship between date. we can try to use "Year Month" column from month table as slicer to solve this.

     


    Best regards,

     

    • Anmolgan's avatar
      Anmolgan
      Post Prodigy
      6 years ago

      But when I take Month Year My Sales volume is not correct, not sure what is the problem.

      • Anmolgan's avatar
        Anmolgan
        Post Prodigy
        6 years ago

        Whenever I take My Month Year field basically its giving me correct royalties but incorrect sales volume, sales volume that this is showing is basically overall sales volume of all the 3 months:

         

         

         

        Any ways to limit that to current month sales and the correct royalty?