Forum Discussion

ChumaAmako's avatar
ChumaAmako
Helper I
5 years ago
Solved

Distinct Count Based on Filter Expressions

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...
Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    5 years ago

    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's avatar
    v-kelly-msft
    5 years ago

    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!

     

ChumaAmako's avatar
ChumaAmako
Helper I
5 years ago

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's avatar
Greg_Deckler
Community Champion
5 years ago

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