Using filter criteria from two different fact tables.

[Pelle]

Hi.
I'm having a problem creating a measure that counts "How many children have had a visit within 14 days after homecoming"
In my model I have one fact table with the "homecoming" events and another fact table with the visits.

In SQL I would have solved it by using Exists like this.

	...
	,count(distinct fv.d_barn_dkey)
	FROM FactVisit fv
	...
	AND
	EXISTS (
		select 
			NULL
		from 
			FactHomecoming f
		where 
			f.D_Aktivitetskod_dKey = 10
			AND fv.D_Barn_dKey = f.D_Barn_dKey
			AND fv.VisitDate <= DATEADD(dd,14,f.HomecomingDate)
		)

But how do I create the Measure?

CountChildrenWithVisitsWithin14daysAfterHomecoming:= 
CALCULATE(
	DISTINCTCOUNT('FactVisit'[D_Barn_dKey])
	;Filter1
	;Filter2
	/*;Missing filter FactVisit[VisitDate] <= FactHomeComing[HomecomingDate] + 14 days */
)

[Pelle · Accepted solution]

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

[Pelle]

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/

[v-yulgu-msft]

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