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

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.