Complex opposite filter doing my head in

Question

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

Answer 1

You might have to add a calculated column that sums all of the "action" columns to use in your filter.

Answer 2

No sure what you mean by that so could you expand?

Answer 3

Maybe calc column not necessary. Try using COUNTA(transactions[Transaction])=0 as last filter argument.

Answer 4

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. 

Answer 5

hi check out the below link.

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