locked
DAX m2m, inactive relationships, and userelationship function not working RRS feed

  • Question

  • Have a look up date dim table that has active and inactive relationships to two different fact tables from two different DB's.  I have a payments table joined to an orders table with a m2m.  I know this is kind of structured weird but just work with me here.  

    My two functions in my orders table are as follows with a supporting max function:

    PMIGrossOrderRevMax:=max(OrdersPMIFacts[SalesTotal])

    PMIGrossOrderRev:=CALCULATE(sumx(DISTINCT(OrdersPMIFacts[OrderID]),[PMIGrossOrderRevMax])

    )

    PMIGrossOrderRevAssigned:=CALCULATE(sumx(DISTINCT(OrdersPMIFacts[OrderID]),[PMIGrossOrderRevMax]),
    USERELATIONSHIP(OrdersPMIFacts[LeadAssignmentDate],DATE_DIMENSIONS[date_value])
    )

    These functions both work fine.  
    Connected to this orders table is a bridge table that is connected to a payments table.  This is a m2m modeling.

    My two functions in my payments table are as follows:  

    PaymentsNet:=CALCULATE(sum(PaymentsPMI[Amount]),
    OrdersPMIFacts)  ->  Works correctly!

    PaymentsNetAssignments:=CALCULATE(sum(PaymentsPMI[Amount]),
    USERELATIONSHIP(OrdersPMIFacts[LeadAssignmentDate],DATE_DIMENSIONS[date_value]),
    OrdersPMIFacts)  

    This last function is where I am having difficulties.  I am using Gerhard Brueckl method to solve for the m2m with slight twist from Marco Russo.  However, it does not appear that the userelationships function work across the m2m and returns the same result as the [PaymentsNet] function.

    Anyone have a solution for this or run into this problem before.  I know I have dealt with this before and ended up solving through my sql query.  Don't really want to go that route here though.  A DAX solution would be much preferred.  Maybe I am missing something very simple.  Usually is the case when I bang my head against the desk.

    Here is the model if you need more context to the problem.

    Wednesday, December 10, 2014 6:36 PM

Answers

  • I don't have a lot of time to play with this, but you need to double up your CALCULATE()s. This example should be the right one, but you'll need to double check:

    PaymentsNetAssignments:=CALCULATE(
        CALCULATE(
            SUM( PaymentsPMI[Amount] )
            , OrdersPMIFacts
        )
        , USERELATIONSHIP( OrdersPMIFacts[LeadAssignmentDate], DATE_DIMENSIONS[date_value] )
    )

    I've run into this before. Essentially you cannot apply your M2M bridge table filter argument and arguments that change the context on that bridge table in the same call to CALCULATE(). Thus, we first determine the inner CALCULATE(), which forces the M2M to behave, then we apply to that M2M context the altered context of USERELATIONSHIP() (technically this is backward, the outer is applied first and propagated to the inner, but I find it easier to think about this way). Let us know if this works.

    • Proposed as answer by Michael Amadi Tuesday, December 16, 2014 8:21 AM
    • Marked as answer by johngabbradley1 Tuesday, December 16, 2014 4:12 PM
    Tuesday, December 16, 2014 12:23 AM

All replies

  • Does anyone have a suggestion for John?

    Thanks!


    Ed Price, Azure & Power BI Customer Program Manager (Blog, Small Basic, Wiki Ninjas, Wiki)

    Answer an interesting question? Create a wiki article about it!

    Friday, December 12, 2014 11:28 PM
  • Maybe this is just one of those unanswerable questions like "Before they invented drawing boards, what did they go back to?"

    Hope one of the brilliant folks can possibly take a swing!
    Monday, December 15, 2014 5:30 PM
  • I don't have a lot of time to play with this, but you need to double up your CALCULATE()s. This example should be the right one, but you'll need to double check:

    PaymentsNetAssignments:=CALCULATE(
        CALCULATE(
            SUM( PaymentsPMI[Amount] )
            , OrdersPMIFacts
        )
        , USERELATIONSHIP( OrdersPMIFacts[LeadAssignmentDate], DATE_DIMENSIONS[date_value] )
    )

    I've run into this before. Essentially you cannot apply your M2M bridge table filter argument and arguments that change the context on that bridge table in the same call to CALCULATE(). Thus, we first determine the inner CALCULATE(), which forces the M2M to behave, then we apply to that M2M context the altered context of USERELATIONSHIP() (technically this is backward, the outer is applied first and propagated to the inner, but I find it easier to think about this way). Let us know if this works.

    • Proposed as answer by Michael Amadi Tuesday, December 16, 2014 8:21 AM
    • Marked as answer by johngabbradley1 Tuesday, December 16, 2014 4:12 PM
    Tuesday, December 16, 2014 12:23 AM
  • Works like a charm.  Thanks very much!
    Tuesday, December 16, 2014 4:12 PM