Active & Inactive relationships

Hi Anonymous ,

It looks like you're trying to combine two different techniques into a single measure.

You usually create a measure as either

-1- A DISCONNECTED measure (i.e. No relationships) and use FILTER in the way you have in order to identify the rows you want to use...

-OR-

-2- You use a CONNECTED measure, whereby you identify the rows you want to use via an active relationship or via USERELATIONSHIP with an inactive relationship.

Personally, I tend to make ALL relationships INACTIVE when I have multiple of them to the same table (usually Calendar to Fact[Created]/Fact[Completed], exactly how you're doing here) so I have complete control over when the relationships come into play (via USERELATIONSHIP).

If both of your relationships were inactive, this measure should work:

_completedUnitsByPeriod =
VAR __periodStart = //however you want the user to select
VAR __periodEnd = //however you want the user to select
RETURN
CALCULATE(
    SUM(Order_table[Units]),
    FILTER(
        Order_table,
        Order_table[Complete Date] <= __periodEnd
        && Order_table[Complete Date] >= __periodStart
        && NOT ISBLANK(Order_table[Complete Date])
    )
)

However, in your current scenario, the following should work fine with the relationship making the row selection:

_completedUnitsByPeriod =
CALCULATE(
    SUM(Order_table[Units]),
    USERELATIONSHIP(Calendar_table[Calendar Date], Order_table[Complete Date])
)

I may have misunderstood exactly what you want your measure to output, but the principle is sound.

Pete