Distinct Count Based on Filter Expressions

[ChumaAmako]

Dear Community,

I have a model with an Accounts, Orders, Order Details, Product Details and Calendar table, with all relationships in place. Please download anonymous pbix file HERE.

I have a challenge creating measures which computes the following from the Order Table:

1. The number of Passive Consumers; This count of the unique consumers who have not purchased in the past 28 days, but has purchased in the last 35 days and has at least 1 previous order.
2. The number of Active Consumers; This count of the unique consumers who have not purchased in the past 28 days. For this, I tried this formula; 
   No of Active Users = CALCULATE(DISTINCTCOUNT('Order Table'[User Email]),FILTER('Order Table',DATESBETWEEN('Calendar'[Date],MAX('Order Table'[Order Start Date])-28,MAX('Order Table'[Order Start Date]))))
   But I get an error when displaying the number in a visual (see pbix file)
   Any help will be really appreciated.
   @amitchandak @Greg_Deckler @mahoneypat 

[Greg_Deckler · Accepted solution]

@ChumaAmako - I would try this way:

No of Active Users =
  VAR __MaxDate = MAXX(ALL('Order Table'),[Order Start Date])
  VAR __MinDate = __MaxDate - 28
  VAR __Table = FILTER(ALL('Order Table'),[Order Start Date]>=__MinDate)
RETURN
  COUNTROWS(DISTINCT(SELECTCOLUMNS(__Table,"Email",[User Email])))

[v-kelly-msft · Accepted solution]

Hi @ChumaAmako ，

Create 2 measures as below：

_Count active = CALCULATE(COUNT('Accounts Table'[Email Address]),FILTER(ALL('Accounts Table'),'Accounts Table'[Email Address]=MAX('Accounts Table'[Email Address])&&'Accounts Table'[Days Since Last Purchase]<=28))

_No of Active Users = 

CALCULATE(DISTINCTCOUNT('Accounts Table'[Email Address]),FILTER('Accounts Table','Accounts Table'[_Count active]>=1)
)

And you will see:

For the related .pbix file,pls see attached.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[Greg_Deckler · Accepted solution]

@ChumaAmako - I would try this way:

No of Active Users =
  VAR __MaxDate = MAXX(ALL('Order Table'),[Order Start Date])
  VAR __MinDate = __MaxDate - 28
  VAR __Table = FILTER(ALL('Order Table'),[Order Start Date]>=__MinDate)
RETURN
  COUNTROWS(DISTINCT(SELECTCOLUMNS(__Table,"Email",[User Email])))

[ChumaAmako]

Hi @Greg_Deckler  thanks a lot for the solution.

Any idea on how I can approach the second measure

The number of Passive Consumers; This count of the unique consumers who have not purchased in the past 28 days, but has purchased in the last 35 days and has at least 1 previous order.

[Greg_Deckler]

@ChumaAmako - Here is a variation on the theme for the passive uers.

No of Passive Users =
  VAR __MaxDate = MAXX(ALL('Order Table'),[Order Start Date])
  VAR __MinDate = __MaxDate - 28
  VAR __MinDate2 = __MaxDate - 35
  VAR __Table1 = SELECTCOLUMNS(FILTER(ALL('Order Table'),[Order Start Date]<__MinDate1),"Email",[User Email]) // users who have bought > 35 days ago.
  VAR __Table2 = SELECTCOLUMNS(FILTER(ALL('Order Table'),[Order Start Date]>=__MinDate1 && [Order Start Date]<__MinDate),"Email",[User Email]) //users bought between 28-35 days agao
  VAR __Table3 = SELECTCOLUMNS(FILTER(ALL('Order Table'),[Order Start Date]>=__MinDate && [Order Start Date]<=__MaxDate),"Email",[User Email]) // users bought in last 28 days
  VAR __Table = INTERSECT(EXCEPT(__Table2,Table3),__Table1)
RETURN
  COUNTROWS(DISTINCT(SELECTCOLUMNS(__Table,"Email",[User Email])))