Forum Discussion
Active & Inactive relationships
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!
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
2 Replies
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