Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Anonymous
Not applicable

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

 

1 ACCEPTED SOLUTION
v-frfei-msft
Community Support
Community Support

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.

 

Capture.PNG

 

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

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

View solution in original post

3 REPLIES 3
v-frfei-msft
Community Support
Community Support

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.

 

Capture.PNG

 

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

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.
Anonymous
Not applicable

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

 

 

Anonymous
Not applicable

Thank you i've tried that and it worked!

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.