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 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.
     
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!

     

4 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    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])))

     

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

         

  • v-kelly-msft's avatar
    v-kelly-msft
    Community Support
    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!