Count number of rows in one table between dates from a different table.

[Brightsider]

So, I'm working with the following data model, based off of Dynamics:

The formula I'm trying to write is one to count the number of Opportunities which were created within two weeks of an appointment being held with a customer in the Activities table, and to group that data by the System User who created the appointment. There are some challenges to this though: An appointment Activity won't have a direct relationship to the Opportunities that are generated from it, so I relate them by way of the Account ID in both tables. Also, the user who made the appointment Activity won't be the user who owns the Opportunity, since the Appointment is set by one Sales Rep and handed off to an Account Manager to handle the opportunities.

This is the point I've managed to get to with the formula:

SUMX(
    ADDCOLUMNS(
        FILTER(Activities, Activities[type] = "Appointment"),
        "NewOpportunities",
        CALCULATE(
            COUNTROWS(Opportunities),
            FILTER(Opportunities, 
                Opportunities[DateCreatedMT] >= (Activities[actualend] - 1) && 
                Opportunities[DateCreatedMT] < [actualend]), (Activities[actualend] + 14)) &&
                Opportunities[parentaccountid] = Activities[accountid]
            ),
            Activities[statecodename] = "Completed",
            Activities[type] = "Appointment"
        )
    ),
    [NewOpportunities]
)

This formula kind of  works: it'll return the number of New Opportunities created after an appointment, but will fail to filter once I try to aggregate it in a table/matrix by the System User. Also, I'm sure there's a less overwrought way of accomplishing what I'm looking for.

Here is the Sample Data.

[Anonymous · Accepted solution]

Hi @Brightsider ,

Try this DAX expression to see if it meets your needs.

Measure = 

VAR _table = ADDCOLUMNS(FILTER('Activities',[type] = "Appointment" && [statecodename] = "Completed"),"NewOpportunities",
    CALCULATE(COUNTROWS('Opportunities'),
    'Opportunities'[DateCreatedMT] >= EARLIER([actualend]) - 1,
    'Opportunities'[DateCreatedMT] < EARLIER([actualend]) + 14,
    'Opportunities'[parentaccountid] = EARLIER([accountid])
    )
)
RETURN CALCULATE(SUMX(_table,[NewOpportunities]))

Best Regards,
Wenbin Zhou

[Brightsider]

So, this solution nearly works! However, if I filter the [title] column from System Users down to just Sales Development Representative, it returns no results, even though I can see from running the measure in the DAX query Editor that it should return numbers for them. What explains this?