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

[jimbob2285]

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 chases, to chase the customer for info on a weekly basis... but I'm struggling to figure out how to do this

I've already created a filtered Activity fact table to exclude completed activities, but I can't figure out how to combine this with the date table to duplicate each row in the filtered Activity fact table for each required weekly chase, which would run until the last date in the Date table... forget about a weekly chase, I can’t even figure out how to do this for a daily chase, i.e. for every future date in the Date table

As soon as a mention Date in a google search all I get is results for creating date tables from fact table, which isn't what I'm trying to do

Here's a link to an example PBIX file: ActivityChase, with my fact tables, filtered table and a manually entered mock-up of the calculated table that I need to create... Any help or advice would be amazing

Thanks

Jim

[jimbob2285]

Hi ToddChitt

For each row in Fact_Activity table i want to filter for Status = "In Progress" and then add multiple rows in the Chase table for a weekly chase, 7 days from created date and every 7 days after that until the end of my date table... this will create a weekly chase KPI that I can measure against our actual recorded chases

Thanks

Jim

[ToddChitt]

Sorry I don't have the exact DAC statement for this, but you will need a few DAX functions. You will need a CROSS JOIN between your FILTERED Activity and FILTERED Dates (filtered on ONE day per week). Then FILTER that again for dates in the future.

Something like this:

My Chase = FILTER (

                               CROSS JOIN (

                                                        FILTER ( 'Activity', [Status] = "In Progress" )

                                                       FILTER ( 'Dates', WEEKDAY('Dates'[The Date] = 1)

                                                 ),

                               [The Date] > TODAY()

                             )

[jimbob2285]

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 · Accepted solution]

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]

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

Cheers

Jim

[ToddChitt]

Can you be more specific about what you are trying to accomplish? Based on a row in Fact_Activity, what do you want in the Chase table? do you want to filter for Status = "In Progress" then add some future date?