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

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,

 

  • Anmolgan's avatar
    Anmolgan
    Post Prodigy
    6 years ago

    v-lid-msft  Thanks for the reply,This works to some extent, but what if I need to user other way around filter, say if I want to use Calender (Year/Month) filter in my report instead of Month/year filter how can i change my dax so that I can use the Master Query filter (the ZSD is my master query where I had built most of the report.)

  • Anmolgan's avatar
    Anmolgan
    Post Prodigy
    6 years ago

    Hi v-lid-msft did you see my last post?

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

    Hi Anmolgan ,

     

    Sorry for our late reply, but based on my test, using the Year/Month Filter in ZSD table , could you please show the exact expected result based on the Tables that you have shared ?

     

     


    Best regards,

     

  • Anmolgan's avatar
    Anmolgan
    Post Prodigy
    6 years ago

    Result should be the same, the one which you have using the previous accepted solution. I just want to use new ZSD measure including the Month Wise column in the measure.