Cohort Analysis Question

[Anonymous]

Dear experts, 

I had this issue while creating the cohort chart 

"The resultset of a query to external data source has exceeded the maximum allowed size of '1000000' rows'".

This happened when I selected all the regions we have (4 regions). Is there any way to show the number of all regions?

Here is my DAX:

Cohort = 
var n_month=MAX('After Months'[Value])
var tbl_current=VALUES('Sales'[Store_ID])
var tbl_n=CALCULATETABLE(VALUES(Sales[Store_ID]),DATEADD(dim_time[Date],n_month,MONTH))
var tbl_inner=INTERSECT(tbl_current,tbl_n)
return
COUNTROWS(tbl_inner)

Another thing is when I tried to create a percentage cohort chart, I wanted the month 0 to be 100% but it showed as less than 100% instead, is it possible to show it 100% regardless the filter I choose?

Here is my current DAX

Cohort % = 
var n_month=MAX('After Months'[Value])
var tbl_current=VALUES('Sales'[Store_ID])
var tbl_n=CALCULATETABLE(VALUES(Sales[Store_ID]),DATEADD(dim_time[Date],n_month,MONTH))
var tbl_inner=INTERSECT(tbl_current,tbl_n)
return
DIVIDE( COUNTROWS(tbl_inner),COUNTROWS(tbl_current))

Thanks a lot in advance!

[amitchandak]

@Anonymous , I have used a little bit of different measures, check if those can help

Power BI Cohort Analysis, Customer Retention %- https://youtu.be/qY1SDF1cwsg

https://community.powerbi.com/t5/Quick-Measures-Gallery/Customer-Retention-Part-3-Period-Of-Stay-Cohort-Analysis/m-p/1393332#M630

[Anonymous]

Hi @amitchandak 

Thanks for your solutions, as far as I see your DAX only measures the new customers in month 1 instead of all customer right? My intention is to see all customers in month 0 and how they retained after that. Can you suggest me how to improvise my current DAX please?