Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
95 | |
76 | |
65 | |
51 |
User | Count |
---|---|
146 | |
109 | |
106 | |
88 | |
61 |