Filter on the Intermediate Measure Group in a Many-To-Many relationship RRS feed

  • Question

  • Hi,

    I have a scenario where we have setup a M2M relationship correctly and the data works as expected.

    So we have our DataMG, the intermediate Dimension that links with the IntermediateMG which in turn links to the DataDimension, so we are able to analyse data from the DataMG by the DataDimension.

    However, we have a scenario where we wish to filter data in the IntermediateMG by date. This MG is linked to a Date dimension, but when we analyse this in the browser window and filter by the Date dimension e.g. Year 2013, we see no change in the data on the DataMG.

    If I add a measure from the IntermediateMG to the browser window I can see that the data does change when applying the Date Dimension filter, but it only filters data based upon this measure. It does not amend the data in the DataMG.

    How can we apply a filter to an intermediate MG, in a M2M relationship.

    Image shows data before filter on Int MG applied (Gift £ is the Data MG, Volunteer Role Type the Data Dimension, Volunteer Count a measure from the Int MG)

    After Date dimension filter applied. The Date dimension is linked to the Int MG.

    Note, the Data MG data does not change. Why is this ? What am I missing ? Thanks for any assistance in advance.

    • Edited by Paul R W Monday, November 18, 2013 11:59 AM
    Monday, November 18, 2013 11:53 AM


All replies

  • Can you show your Dimension Usage tab too please? Have you specified that you want a many-to-many relationship between your Date dimension and the DataMG measure group, using IntermediateMG as the intermediate measure group?


    Check out my MS BI blog I also do SSAS, PowerPivot, MDX and DAX consultancy and run public SQL Server and BI training courses in the UK

    • Marked as answer by Paul R W Monday, November 18, 2013 2:46 PM
    Monday, November 18, 2013 12:17 PM
  • Of course ! Now it's pointed out to me that makes complete sense.

    I originally changed this only to a Regular relationship which I can see now why it would only change results for the Volunteer measure.

    I just amended the Date Dimension to DataMG to a M2M relationship using the IntMG as per your suggestion and it works.

    Thanks a lot Chris.

    Good talk on the PowerBI and PowerPivot stuff last week at SQLRelay in Reading by the way.

    Monday, November 18, 2013 2:45 PM