Forum Discussion
Using filter criteria from two different fact tables.
- 8 years ago
So I think I've solved it.
Just for the record: There should be just one homecoming date per child.
To be able to compare the two dates from the two fact tables I created two calculated columns in DimChild.
First one like this.HomecommingDate =
CALCULATE(
MIN('F_Homecomming'[ActivityDate].[Date]);
)
The other one (because I wasn't able to solve it in the first step) like this.
HomecommingDate_Plus14Days =
DATEADD('D_Child'[HomecommingDate].[Date];14;DAY)
Then I used this calculated column in the comparsion when creating the measure.
CALCULATE(
DISTINCTCOUNT('F_Visit'[D_Child_dKey])
;...
;'...
;FILTER('F_Visit';
'F_Visit'[VisitDate] <= RELATED('D_Child'[HomecommingDate_Plus14Days])
)
)
Feel free to comment if there is a better way of doing it...
Cheers
So I think I've solved it.
Just for the record: There should be just one homecoming date per child.
To be able to compare the two dates from the two fact tables I created two calculated columns in DimChild.
First one like this.
HomecommingDate =
CALCULATE(
MIN('F_Homecomming'[ActivityDate].[Date]);
)
The other one (because I wasn't able to solve it in the first step) like this.
HomecommingDate_Plus14Days =
DATEADD('D_Child'[HomecommingDate].[Date];14;DAY)
Then I used this calculated column in the comparsion when creating the measure.
CALCULATE(
DISTINCTCOUNT('F_Visit'[D_Child_dKey])
;...
;'...
;FILTER('F_Visit';
'F_Visit'[VisitDate] <= RELATED('D_Child'[HomecommingDate_Plus14Days])
)
)
Feel free to comment if there is a better way of doing it...
Cheers