Forum Discussion

jakubk1's avatar
jakubk1
Regular Visitor
4 years ago

create filter flag based on filtered subtotal

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

 

Need Help

5 Replies

  • tamerj1's avatar
    tamerj1
    Community Champion
    4 years ago

    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's avatar
      jakubk1
      Regular Visitor
      4 years ago

      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

       

  • tamerj1's avatar
    tamerj1
    Community Champion
    4 years ago

    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]
    )
)
  • jakubk1's avatar
    jakubk1
    Regular Visitor
    4 years ago

    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

  • jakubk1's avatar
    jakubk1
    Regular Visitor
    4 years ago

    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
    )
    )