Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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 */ )
Solved! Go to 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
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/
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
User | Count |
---|---|
125 | |
106 | |
99 | |
63 | |
62 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |