Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

cohort analysis

H there

This must be easy but my DAX is officialy rusty.

 

So I got list of customers and I want to count number of customers that appear in multiple conditions exclusively.

 

For example I got slicers say product A, Product B and Product C.

 

I want customers to count customers who bought all products, or some products (depending on what I chose on my slicers)..

 

The problem I have now is that when i slice by Product A and Product B, I got those customers that only bought Product A or only bought Product B or Both,.... i dont want that. I only want ALL as per my slicers....

 

A bit like association rules :)

 

Hope this makes sense.

 

Help please :)

 

Thanks so much

 

Need Help
  • v-frfei-msft's avatar
    v-frfei-msft
    7 years ago

    Hi Anonymous,

     

    Here I made one sample for your reference.

     

    Enter the sampe data and create the measures as below.

     

    countselected = IF(ISFILTERED(Table1[Product]),CALCULATE(DISTINCTCOUNT(Table1[Product]),ALLSELECTED(Table1)),BLANK())
    Countproduct = IF(ISFILTERED(Table1[Product]),CALCULATE(DISTINCTCOUNT(Table1[Product]),ALLSELECTED(Table1[Product])),BLANK())
    countall = CALCULATE(COUNT(Table1[customers]),FILTER(ALLSELECTED(Table1),[Countproduct]=[countselected]))
    Measure 2 = IF([Countproduct]=[countselected],1,0)

    Then we can create a table visual that make the visual is filterd by the Measure 2. You can refer to the result as the picture.

     

     

    For more details, please check the pbix as attached. If it doesn't meet your requirement, kindly share your sample data and excepted result to me.

     

    https://www.dropbox.com/s/k4v2urmz08forys/cohort%20analysis.pbix?dl=0

     

    Regards,

    Frank

3 Replies

  • v-frfei-msft's avatar
    v-frfei-msft
    Community Support
    7 years ago

    Hi Anonymous,

     

    Here I made one sample for your reference.

     

    Enter the sampe data and create the measures as below.

     

    countselected = IF(ISFILTERED(Table1[Product]),CALCULATE(DISTINCTCOUNT(Table1[Product]),ALLSELECTED(Table1)),BLANK())
    Countproduct = IF(ISFILTERED(Table1[Product]),CALCULATE(DISTINCTCOUNT(Table1[Product]),ALLSELECTED(Table1[Product])),BLANK())
    countall = CALCULATE(COUNT(Table1[customers]),FILTER(ALLSELECTED(Table1),[Countproduct]=[countselected]))
    Measure 2 = IF([Countproduct]=[countselected],1,0)

    Then we can create a table visual that make the visual is filterd by the Measure 2. You can refer to the result as the picture.

     

     

    For more details, please check the pbix as attached. If it doesn't meet your requirement, kindly share your sample data and excepted result to me.

     

    https://www.dropbox.com/s/k4v2urmz08forys/cohort%20analysis.pbix?dl=0

     

    Regards,

    Frank

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Thank you i've tried that and it worked!

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      v-frfei-msft hi again,

      I wonder if we can improve dax below to force order of slicers?

      For example

      If I want to know who buys Product A then go on to Product B. 

      Dax below works if both products are selected in slicers but it doesnt tell me what happened first. I can tell this via viewing the data in table but only want to count those that buy Product A first then Product B,

       

      Hope it makes sense.

      Thank you for your help.