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 anoth...
  • Pelle's avatar
    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