Forum Discussion
Using filter criteria from two different fact tables.
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
A small correction.
I've changed the formula when creating the calculated column
HomecommingDate_Plus14Days =DATEADD('D_Child'[HomecommingDate].[Date];14;DAY)
to
HomecommingDate_Plus14Days = 'D_Child'[HomecommingDate].[Date] + 14
because the strange behavior of the DATEADD that gaved me BLANK values on some rows instead of the new date.
The new calculated date must exists as a value in the original column (HomecommingDate) otherwize it turns to BLANK.
Read more here: https://blog.crossjoin.co.uk/2012/01/06/the-dax-dateadd-function-and-missing-dates/