Forum Discussion

Pelle's avatar
Pelle
Frequent Visitor
8 years ago
Solved

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
			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's avatar
    Pelle
    8 years ago

    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

3 Replies

  • Pelle's avatar
    Pelle
    Frequent Visitor
    8 years ago

    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

    • v-yulgu-msft's avatar
      v-yulgu-msft
      Microsoft Employee
      8 years ago

      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

    • Pelle's avatar
      Pelle
      Frequent Visitor
      8 years ago

      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/