Using summarize and userelationship to generate a sum based on a condition

Question

Story:

I have two date columns in my fact table, one is for orderdate and the second is for orderdate/refund/cancelled date.

I created two relationships between the date table and the fact table.

Active: Date > OrderDate
Inactive: Date > OtherDate

I would like to sum the # of refunds per day using the inactive relationship.

What I tried:

Returns =
VAR summary =
    SUMMARIZE (
        FILTER ( Query1, Query1[kind] = "refund" ),
        Query1[orderId],
        "returns", MAX ( Query1[amount] )
    )
RETURN
    CALCULATE (
        MAX ( Query1[amount] ),
        USERELATIONSHIP ( Query1[OtherDate], DateTable[Date] ),
        summary
    )

For some reason, it's using the active date column. Any suggestion on how to fix the above formula?

Alexis Olson Alexis Olson · Accepted Answer · 2019-07-09T17:15:56

I'm not sure I understand how you are intending to use the summary variable here, but note that USERELATIONSHIP doesn't affect it at all since it's already computed.

You might not need that variable at all. Try this:

Returns =
CALCULATE (
    MAX ( Query1[amount] ),
    USERELATIONSHIP ( Query1[OtherDate], DateTable[Date] ),
    Query1[kind] = "refund"
)

Using summarize and userelationship to generate a sum based on a condition

1 Answers