none
Multiple Many-To-Many with Single Measure

    Question

  • Hi everyone,

    We are trying to reproduce behavior in PowerPivot which seems fairly straightforward in MD cubes but is elusive to a DAX novice.

    The goal is two-fold:

    1. Support filtering/slicing on multiple attributes ("Preference", "Territory") both of which have a many-to-many relationships with the Sales fact table.

    2. Support a single measure called "Sales" in the semantic layer that is correct at all levels of aggregation regardless of the filtered value combination (including All).  NOTE: Some customers do not have any corresponding attribute records in the data, i.e. Unknown.

    Could someone provide an example of DAX that would be used to produce the "Sales" measure, irrespective of which slicer is used.

    Here's the setup:

    PowerPivot Model

    The data looks like this:

    Data

    Thanks for any insights you can offer!

    Jeff


    • Edited by jkanelblade Sunday, October 20, 2013 4:29 PM formatting
    Sunday, October 20, 2013 4:23 PM

Answers

  • Jeff,

    have you tried this?

    Sales:=CALCULATE (
        SUM('customer_sales'[sales_amount]),
        customer_territory,
       
    customer_preference
    )


    Marco Russo http://ssasworkshop.com http://www.sqlbi.com http://sqlblog.com/blogs/marco_russo

    • Marked as answer by jkanelblade Tuesday, October 22, 2013 3:41 PM
    Monday, October 21, 2013 4:22 PM

All replies

  • After some research, I found the answer:

    Sales:=CALCULATE(
    SUM('customer_sales'[sales_amount]),
    FILTER('customer_territory',CALCULATE(COUNTROWS('customer_territory))>0),
    FILTER('customer_preference',CALCULATE(COUNTROWS('customer_preference'))>0)
    )

    This is implemented as a Calculation in customer_sales  rather than as a column.

    In order to make this work it was necessary to manually add placeholder "Unknown" records for missing customer_keys in the M2M tables.  It remains unclear how to account for the missing records through DAX.  I'll research some more and will perhaps ask in another post. 

    Jeff

    Monday, October 21, 2013 2:32 AM
  • Jeff,

    have you tried this?

    Sales:=CALCULATE (
        SUM('customer_sales'[sales_amount]),
        customer_territory,
       
    customer_preference
    )


    Marco Russo http://ssasworkshop.com http://www.sqlbi.com http://sqlblog.com/blogs/marco_russo

    • Marked as answer by jkanelblade Tuesday, October 22, 2013 3:41 PM
    Monday, October 21, 2013 4:22 PM
  • Hi Marco.

    Thank you for the simplification.  I even took it one step further by adding master tables for "Preference" and "Territory".  I don't fully understand why this works...headed off now to read sqlbi.com.

    Sales:=CALCULATE(
    SUM('customer_sales'[sales_amount]),
    customer_territory,
    territory,
    customer_preference,
    preference
    )


    • Edited by jkanelblade Tuesday, October 22, 2013 4:02 PM
    Tuesday, October 22, 2013 3:41 PM
  • Even using the new model, you should get the same result by using this formula:

    Sales:=CALCULATE (
        SUM('customer_sales'[sales_amount]),
        customer_territory,
       
    customer_preference
    )

    I suggest you reading this article (and related link/papers):http://www.sqlbi.com/articles/optimize-many-to-many-calculation-in-dax-with-summarize-and-cross-table-filtering


    Marco Russo http://ssasworkshop.com http://www.sqlbi.com http://sqlblog.com/blogs/marco_russo

    Tuesday, October 22, 2013 10:56 PM
  • For posterity, here are links to some amazing articles on how CALCULATE works.  One is by Marco.

    http://sqlblog.com/blogs/marco_russo/archive/2010/01/03/how-calculate-works-in-dax.aspx

    http://mdxdax.blogspot.com/2011/03/logic-behind-magic-of-dax-cross-table.html

    Thanks again, Marco.

    • Edited by jkanelblade Thursday, October 24, 2013 10:39 PM
    Thursday, October 24, 2013 1:04 PM