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 ...
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,

     

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?

Anmolgan's avatar
Anmolgan
Post Prodigy
6 years ago

I found a solution to solve the Many to Many problem, I need to use 2 month filters from both of my tables this gives me correct royalties and correct sales, but the problem now is I cannot have 2 month slicers inside my report, is there any way i can apply a page level filter on a month slicer so that it remain in sync with the actual slicer applied on the visual, so user does not have to change 2 slicers every time he uses the reports.

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

    Hi Anmolgan ,

     

    Glad to hear that you have resolved your problem. Thank you for sharing this wonderful solution, we have learned a lot from it. if you have any other questions about this scenario, please kindly ask here and we will try to resolve it.


    Best regards,

     

    • Anmolgan's avatar
      Anmolgan
      Post Prodigy
      6 years ago

      I need to use 2 month filters from both of my tables this gives me correct royalties and correct sales, but the problem now is I cannot have 2 month slicers inside my report, is there any way i can apply a page level filter on a month slicer so that it remain in sync with the actual slicer applied on the visual, so user does not have to change 2 slicers every time he uses the reports???

      • v-lid-msft's avatar
        v-lid-msft
        Community Support
        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,