CODEWITHSUNDEEP
powerbidax
Roger Steinberg
Roger Steinberg

Reputation: 1604

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

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.

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?

Upvotes: 0

Views: 3522

Answers (1)

Alexis Olson
Alexis Olson

Reputation: 40244

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"
)

Upvotes: 1

Related Questions