cancel
Showing results for
Did you mean:
Highlighted
Frequent Visitor

## Using filter criteria from two different fact tables.

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
)```

But how do I create the Measure?

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

1 ACCEPTED SOLUTION

Accepted Solutions
Frequent Visitor

## Re: 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 =

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

3 REPLIES 3
Frequent Visitor

## Re: 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 =

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

Super Contributor

## Re: Using filter criteria from two different fact tables.

Hi @Pelle,

Regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Frequent Visitor

## Re: Using filter criteria from two different fact tables.

A small correction.
I've changed the formula when creating the calculated column

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.

Announcements

#### Back to School Contest

Engage and empower students with Power BI!

#### Watch Sessions On Demand!

Continue your learning in our online communities.

#### Summit Australia 2019

Travel to Melbourne and network with thousands of peers!

#### PBI Community Highlights

Check out what's new in the Power BI Community!

Top Ideas
Top Kudoed Authors
Users Online
Currently online: 99 members 1,537 guests
Recent signins: