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
Hi Pelle,
Thanks for your sharing. Would you please mark your sharing solution as an answer so that it can benefit more users?
Regards,
Yuliana Gu