locked
How can I create a DAX measure that calculates a total from one table that groups by a value in a related table. RRS feed

  • Question

  • Hello,
    Got another question for anyone who is willing to help.  I'm trying to create a measure that sums the RejectVolume from the Reject table for each distinct Reject Reason (AEExceptionReason in the Exception table). The Exception table is related to the Reject table via the RejectID - one reject can have many reject reasons, i.e. AEException(ID).  However, I can't seem to find a calculation that works using relatedtable, summarize etc.
    Thanks,
    Ben



    • Edited by blezin Thursday, April 2, 2015 4:14 PM
    Friday, March 27, 2015 12:46 AM

Answers

All replies

  • Hello,
    Got another question for anyone who is willing to help.  I'm trying to create a measure that sums the RejectVolume from the Reject table for each distinct Reject Reason (AEExceptionReason in the Exception table). The Exception table is related to the Reject table via the RejectID - one reject can have many reject reasons, i.e. AEException(ID).  However, I can't seem to find a calculation that works using relatedtable, summarize etc.
    Thanks,
    Ben




    • Merged by Charlie Liao Friday, March 27, 2015 5:22 AM duplicate thread
    • Edited by blezin Thursday, April 2, 2015 4:12 PM
    Friday, March 27, 2015 12:46 AM
  • try this:

    CALCULATE(MAX(Reject[RejectVolume]))


    Imke

    Friday, March 27, 2015 5:58 AM
    Answerer
  • Can you clarify what you want to obtain with an example? you should simply apply the many-to-many pattern, but it's not clear which result you are looking for.

    Marco Russo (Blog, Twitter, LinkedIn) - sqlbi.com: Articles, Videos, Tools, Consultancy, Training
    Format with DAX Formatter and design with DAX Patterns. Learn Power Pivot and SSAS Tabular.

    • Proposed as answer by Michael Amadi Sunday, April 19, 2015 12:07 AM
    • Marked as answer by Michael Amadi Thursday, April 30, 2015 6:05 PM
    Thursday, April 2, 2015 8:38 AM
  • Yes, I resolved this by creating a separate bridge table with distinct values for the exception reason.

    Thanks,

    Ben


    Ben Lezin

    • Marked as answer by Michael Amadi Thursday, April 30, 2015 6:06 PM
    Thursday, April 2, 2015 4:10 PM