Complex opposite filter doing my head in

# Complex opposite filter doing my head in

• Saturday, April 28, 2012 10:43 AM

I have a table of transactions by User ID where the "transaction" can be add, spend, redeem etc. I define "Active" as "spend" in last 60 days.  The measure below works perfectly to calculate the number of distinct Active users at the end of each "Visit month" (I use EOM as Visit month is format 1st of each month).

`=CALCULATE(countrows(distinct(transactions[User ID])),filter(transactions,AND(and(transactions[Date]>EOMONTH(WebStats[Visit Month],0)-60,transactions[Date]<EOMONTH(WebStats[Visit Month],0)+1),transactions[Transaction]="spend")))`

I want to now calculate the number of "inactive" users, but can't take a simple total user- active users approach as going to be used in an even more compelx " reactivated users" calculation.  I cannot for the life of me work out how to filter the table to get the right result.  The problem is you can't just put a NOT around the =spend clause as although this removes all the lines where they have spent it will leave in lines where those active users performed some other action and therefore get caught in the DISTINCT calc.   What I need to do is filter this date window for all transactions where the User has done any action including "spend". I understand the logic, but not how to perform it!

Any help appreciated. Mike

### All Replies

• Saturday, April 28, 2012 3:18 PM

You might have to add a calculated column that sums all of the "action" columns to use in your filter.
• Saturday, April 28, 2012 4:36 PM

No sure what you mean by that so could you expand?
• Sunday, April 29, 2012 2:45 AM

Maybe calc column not necessary. Try using COUNTA(transactions[Transaction])=0 as last filter argument.
• Sunday, April 29, 2012 11:43 AM

Afraid that returns blanks. I'm not sure exaclty what that means as the User ID wont apear in the transaction able if they have made no transactions. Also there are 7 other transaction types wihch don't qulaify them as "active". For example a User loads money on their account and creates a transaction "add". They are inactive.  If they spend there is a 2nd transaction "spend" and they are now active.  if I filter out all the rows with "spend" I still have a row for this user due to the "Add" transaction so would be included in the distinct count.  To ge to the true inactives I need somehow to also remove any transactions where the User ID is the same as Users who have a "spend" line.
• Edited by Thursday, May 03, 2012 8:26 AM
•
• Wednesday, May 02, 2012 8:55 AM

hi check out the below link.

http://www.guardian.co.uk/science/2011/may/18/migraine-postdrome-research