DAX help - how to calculate the customer share with orders in 8 out of the last 10 weeks.

[imlaug]

Hey,

i am trying to calculate the number of customers who have placed an order in 8 of the last 10 weeks. Then again with orders in 4-7 of the 10 weeks, and 3 more scenarios after that.

I have managed to make a crappy calculation, however it is really slow when it has to calculate it across million of lines.

The calculations are correct in the sample file i have attached(link via wetransfer, as i cant attach), but they are just SO slow, that in the real file, it cant process it. Can someone show me a better way of doing the calculations. 

It has to by dynamical, so the measures will recalculate based on the filters i have selected. I have inserted a filter on the page as an example.

Here is the crappy dax that counts the number of customers that have orders in 8-10 of the 10 weeks.

Weekly test - part 1 = 
var test = 
    SUMMARIZE(Sales;
        Sales[STORE_COMPANIES_ID];
    --    Dim_Customers_Detail[COUNTRY];
        "week_1";if(CALCULATE(DISTINCTCOUNT(Sales[SALES_ORDER_NUMBER]);Sales[NOOS - NON NOOS]="NOOS";'Calendar'[Last 7 days]="yes")>0;1;0); 
        "week_2";if(CALCULATE(DISTINCTCOUNT(Sales[SALES_ORDER_NUMBER]);Sales[NOOS - NON NOOS]="NOOS";'Calendar'[Last 7-14 days]="yes")>0;1;0); 
        "week_3"; if(CALCULATE(DISTINCTCOUNT(Sales[SALES_ORDER_NUMBER]);Sales[NOOS - NON NOOS]="NOOS";'Calendar'[Last 14-21 days]="yes")>0;1;0); 
        "week_4"; if(CALCULATE(DISTINCTCOUNT(Sales[SALES_ORDER_NUMBER]);Sales[NOOS - NON NOOS]="NOOS";'Calendar'[Last 21-28 days]="yes")>0;1;0); 
        "week_5"; if(CALCULATE(DISTINCTCOUNT(Sales[SALES_ORDER_NUMBER]);Sales[NOOS - NON NOOS]="NOOS";'Calendar'[Last 28-35 days]="yes")>0;1;0); 
        "week_6"; if(CALCULATE(DISTINCTCOUNT(Sales[SALES_ORDER_NUMBER]);Sales[NOOS - NON NOOS]="NOOS";'Calendar'[Last 35-42 days]="yes")>0;1;0); 
        "week_7"; if(CALCULATE(DISTINCTCOUNT(Sales[SALES_ORDER_NUMBER]);Sales[NOOS - NON NOOS]="NOOS";'Calendar'[Last 42-49 days]="yes")>0;1;0); 
        "week_8"; if(CALCULATE(DISTINCTCOUNT(Sales[SALES_ORDER_NUMBER]);Sales[NOOS - NON NOOS]="NOOS";'Calendar'[Last 49-56 days]="yes")>0;1;0); 
        "week_9"; if(CALCULATE(DISTINCTCOUNT(Sales[SALES_ORDER_NUMBER]);Sales[NOOS - NON NOOS]="NOOS";'Calendar'[Last 56-63 days]="yes")>0;1;0); 
        "week_10"; if(CALCULATE(DISTINCTCOUNT(Sales[SALES_ORDER_NUMBER]);Sales[NOOS - NON NOOS]="NOOS";'Calendar'[Last 63-70 days]="yes")>0;1;0)
        )

var test1 = 
 SUMMARIZE(
    test;Sales[STORE_COMPANIES_ID]
    ;
    --Dim_Customers_Detail[COUNTRY];
    "Summary_Column"; SUMX(FILTER(test; Sales[STORE_COMPANIES_ID] = EARLIER(Sales[STORE_COMPANIES_ID]));
     [week_1] + [week_2] + [week_3]+[week_4] + [week_5] + [week_6]+[week_7] + [week_8] + [week_9] + [week_10])
)
var test2 = FILTER(test1;[Summary_Column]>=8)
var test3 = COUNTROWS(test2)



return 
test3

[tamerj1 · Accepted solution]

Hi @imlaug 
Please refer to the attached optimized sample file for more details.

[imlaug]

Thank you! Appears to be working

[some_bih]

Hi @imlaug your issue is probably on DISTINCTCOUNT usage as general. I am not sure did you have time to check link for related topic.

DISTINCTCOUNT is "slow" in general while you do it over Sales as fact table which is two big issue to get better performance. 

Use DAX Studio to check your SE and other related factor.