Limit Summarized Table Size

[bangsheadondesk]

I need to count total and average program counts, both at the individual record level ([Opp Id]) and as a total group (all selected records from the 'Opportunity' table).  

This requires a DISTINCTCOUNT of [Program Name] at the individual Opp Id level, then adding those together when multiple [Opp Id]s are selected.  (ie--If one person interacts multiple times with one program, it should only count 1 time.  But if two separate people interact with the same [Program Name], it should be counted twice in the final calculation.)

I have a working measure:

Campaign Touches by Opp =

SUMX(

    SUMMARIZE(

        Opportunity,

        Opportunity[Opp ID],

        "UniqueActivityCount",

        CALCULATE(DISTINCTCOUNT(ACTIVITIES[Program Name]), USERELATIONSHIP(Opportunity[Contact Id], LEADS[Contact Id]), CROSSFILTER('Date Opp'[Date], Opportunity[Created Date], OneWay))),

        [UniqueActivityCount])

The first problem is table size.  The final report will be primarily direct query from two sources (Snowflake and Salesforce), but charts will usually still involve millions of lines at a time.  I'm building with only a limited subset of data, and if only a few records are selected, the measure is fine.  If even one month's worth of the full dataset is called at once, the time needed to compile is staggering.

The second problem is the complexity of the relationships.  The report has multiple components, so finding the best combination of active/inactive relationships to work well across all pages, with and without measures, is frustrating.  The data is also filthy (the worst I've ever seen), and full of errors/empty fields/duplicates, which require careful cross-filtering.

I want an efficient way to limit the SUMMARIZE function to just the filtered [Opp Id]s on the page.  I have tried both adjusting the current measure and introducing a summarized table as a variable for the measure using ALLEXCEPT, but that is complicated given the many other filters to work around.  Doing the same thing with ALLSELECTED sums everything on individual records or brings in far more data than needed.  Would KEEPFILTERS work somehow?  Is there any way to streamline my current measure, or can you recommend a better option?

[lbendlin]

The final report will be primarily direct query from two sources (Snowflake and Salesforce), 

There is no Direct Query connector to Salesforce.

Sounds like you might benefit from the Aggregation feature and from the Dual Storage mode on smaller dimension tables that support Direct Query.