Forum Discussion

jimbob2285's avatar
jimbob2285
Icon for Advocate IV rankAdvocate IV
3 years ago
Solved

Create an activity chase table from an activity fact table and date table

Hi Everyone   I'll try and word this as concisely as I can... I have a fact table of activities, as well as a Date table (that goes into the future) - I need to create a new fact table of activity ...
Need Help
  • ToddChitt's avatar
    ToddChitt
    3 years ago

    In the CROSS JOIN, do not filter the Dim Date table. That gets you a Chase date for every day of the week for every Chase Activity. Wrap your final FILTER in another FILTER where the WEEKDAY of the Chase Activity = the WEEKDAY of the Dim Date column:

    FILTER (

        FILTER ( CROSS JOIN (

                  FILTER ( Activity..."In Progress"),

                   'Dim Date'

                  )

         , 'Dim Date' [My Date] >= TODAY()

        )

       WEEKDAY ( 'Activity'[Date] ) = WEEKDAY ('Dim Date'[The Date] )

         

jimbob2285's avatar
jimbob2285
Icon for Advocate IV rankAdvocate IV
3 years ago

Thanks fo you help and propmt response, CROSS JOIN worked perfectly and produced almost the table that I'm after:

 

ActivityChase = FILTER(
    CROSSJOIN(
        FILTER('Fact_Activity', 'Fact_Activity'[Status] = "In Progress"),
        FILTER('Dim_Date', WEEKDAY('Dim_Date'[Date], 2) = 1)
    ),
    'Dim_Date'[Date] > TODAY() && 'Dim_Date'[WorkDay] = "Y"
)
 
But that puts all the chases for each activity on the same day of the week, which makes for a busy day for the team... I need the chase for each activity to be on the same day that activity was created (created on a Monday, chases on a Monday, created on a Tuesday, chases on a Tuesday, etc)... But when a modify:
        FILTER('Dim_Date', WEEKDAY('Dim_Date'[Date], 2) = 1)
To:
        FILTER('Dim_Date', WEEKDAY('Dim_Date'[Date], 2) = WEEKDAY('Fact_Activity', 2))
I get the error:
        The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value.
 
I'm guessing I'm getting this because there's no relationship between the activities in the new table (this is creating) and the activity table, but how do i fix this?... Any ideas?
 
Cheers
Jim
ToddChitt's avatar
ToddChitt
Icon for Super User rankSuper User
3 years ago

In the CROSS JOIN, do not filter the Dim Date table. That gets you a Chase date for every day of the week for every Chase Activity. Wrap your final FILTER in another FILTER where the WEEKDAY of the Chase Activity = the WEEKDAY of the Dim Date column:

FILTER (

    FILTER ( CROSS JOIN (

              FILTER ( Activity..."In Progress"),

               'Dim Date'

              )

     , 'Dim Date' [My Date] >= TODAY()

    )

   WEEKDAY ( 'Activity'[Date] ) = WEEKDAY ('Dim Date'[The Date] )

     

  • jimbob2285's avatar
    jimbob2285
    Icon for Advocate IV rankAdvocate IV
    3 years ago

    Thanks so much for all your help, that worked an absolute treat and has taght me so much

     

    Cheers

    Jim