Complex opposite filter doing my head in


  • 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

    2012年4月28日 上午 10:43



  • You might have to add a calculated column that sums all of the "action" columns to use in your filter.
    2012年4月28日 下午 03:18
  • No sure what you mean by that so could you expand?
    2012年4月28日 下午 04:36
  • Maybe calc column not necessary. Try using COUNTA(transactions[Transaction])=0 as last filter argument.
    2012年4月29日 上午 02:45
  • 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. 
    • 已編輯 masplin 2012年5月3日 上午 08:26
    2012年4月29日 上午 11:43
  • Is this still an issue? 


    Ed Price, SQL Server Customer Program Manager (Blog, Small Basic, Wiki Ninjas, Wiki)

    Answer an interesting question? Create a wiki article about it!

    2013年9月18日 下午 09:09
  • can remember so happy to close!
    2013年9月19日 下午 03:07