create filter flag based on filtered subtotal

[jakubk1]

Hi all,

The 1-2 year gap between working with dax as my role focus changes seems to wipe my brain every time

I'm struggling to remember how to do a filtered subtotal that I can then use to filter a viz that doesn't take 10minutes to run

as an analyst i want to know how many unique people performed at least x number of actions within the last x days

I have a 10M+ row activity table with an email, date and action

I'm only interested in the people that have performed at least 3 actions in the last 10 days

searches value and days value are based on the parameters

max date is the max date in the table across all contacts

filtered date is max date - days value

I want a flag that I can filter on, so only the top 4 rows will be on the viz for this particular person (keep in mind this needs to work across 3-4 mil distinct contacts and 15mil activities)

I can get a '4' to appear but it appears against every single row not just the most recent 4. Adding extra filters will make it look ok but it doesn't get any faster which to me means it's still doing it against the whole table rather than just the subset

What I have working is just too slow.

-- check to see if it's an activity i care about

ActivityInRange = if('DataMart FactActivitySnapshot'[filterdate]<=max('DataMart FactActivitySnapshot'[ActivityCreated]),1,BLANK())

-- count # of activities in range for each contact

ValidActivityCount = calculate(count('DataMart FactActivitySnapshot'[ActivityID]),filter(allexcept('DataMart FactActivitySnapshot','DataMart FactActivitySnapshot'[ContactEmail]),'DataMart FactActivitySnapshot'[ActivityInRange]=1))

-- flag to indicate that this activity belongs to a contact which satisfies the # of activities and date range parameters

Search Activity = if('DataMart FactActivitySnapshot'[ValidActivityCount] >= Searches[Searches Value] && [filteredcount] = 1,1,BLANK())

I then need to use seach activity to create the measures i actually want to show (count & distinct count) with drill downs on hierarchies etc

[jakubk1]

I've messed around with it - i had some page level filters that i didn't need anymore so once i took it off it sped up - it actually rendered w/o throwing an OOM error

@tamerj1 can you explain or point to some doco on why the bolded part below (sorry this new forum isn't letting me edit quoted code) is so much faster than filtering using the ActivityInRange flag? maybe it's my carryover from MDX but I always try to break up my calcs into separate parts that I can debug individually instead of throwing it all into one long one

ValidActivityCount =
CALCULATE (
DISTINCTCOUNT ( 'DataMart FactActivitySnapshot'[ActivityID] ),
FILTER (
CALCULATETABLE (
'DataMart FactActivitySnapshot',
ALLEXCEPT (
'DataMart FactActivitySnapshot',
'DataMart FactActivitySnapshot'[ContactEmail]
)
),
'DataMart FactActivitySnapshot'[ActivityCreated] >= [filterdate] --<== this part
)
)

[jakubk1]

I can't add the activityinrange as a filter. I can drag it into filters on this visual but the dropdown, values textbox and apply button are greyed out

I dont think applying the filter that way will work because I still need to use the validactivitycount as a filter for other measures where i check that the activity count is above the what if param threshold

[tamerj1]

Hi @jakubk1 
Lets split it into two steps. First place the measure [ActivityInRange] in the filter pane, select "Is" and type 1 then apply the filter

ActivityInRange =
IF (
    [filterdate] <= MAX ( 'DataMart FactActivitySnapshot'[ActivityCreated] ),
    1
)

Then insert the [ValidActivityCount] into the table visual. Try this version:

ValidActivityCount =
COUNTROWS (
    FILTER (
        CALCULATETABLE (
            VALUES ( 'DataMart FactActivitySnapshot'[ActivityID] ),
            ALLEXCEPT (
                'DataMart FactActivitySnapshot',
                'DataMart FactActivitySnapshot'[ContactEmail]
            )
        ),
        'DataMart FactActivitySnapshot'[ActivityCreated] >= [filterdate]
    )
)

[tamerj1]

Hi @jakubk1 
Please try

ValidActivityCount =
CALCULATE (
    DISTINCTCOUNT ( 'DataMart FactActivitySnapshot'[ActivityID] ),
    FILTER (
        CALCULATETABLE (
            'DataMart FactActivitySnapshot',
            ALLEXCEPT (
                'DataMart FactActivitySnapshot',
                'DataMart FactActivitySnapshot'[ContactEmail]
            )
        ),
        'DataMart FactActivitySnapshot'[ActivityCreated] >= [filterdate]
    )
)

[jakubk1]

Hi, what should happen with that new measure?

It's still smearing the data across all the rows

(it's the _new in the screenshot)

I tried replacing my valid activity count with the new version in the downstream calcs but it didn't make them any faster