Active & Inactive relationships

[Anonymous]

Hello, 

I am working on a model that, simplified, looks like the one below. The order table (fact table) is linked to a calendar through 2 relationships:
Create date and calendar date - active

Complete date and calendar date - inactive

The tables look like the one below:
Calendar_table

  
Order_table

Complete blank can be blank if the order is not complete yet. 

I need to calculate the sum of units for a specific period of time for only the complete orders. The measure I created looks like this:
Units = CALCULATE(SUM(Order_table[Units]), 

            USERELATIONSHIP(Order_table[Complete Date], Calendar_Table[Calendar Date]), //to make the relationship active

            FILTER(ALL(Calendar_table), Calendar_table[Calendar Date] >= period_start && Calendar_table[Calendar Date]<=period_end))


I am not sure why, the only orders I get are the ones without any dates (the incomplete ones). What am I doing wrong? 

I appreciate your help! 

[Anonymous]

The first option worked for me. Thank you! 

[BA_Pete · Accepted solution]

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