AVERAGE PER CLIENT WITH A FILTER

[Kaatiiaa]

Hi

I have a table of sales events (one row is one product, one order may have multiple rows).

I want to calculate average number of orders per client (identified by email address) and include in this measure only orders placed via certain domains/shops. In my reports I want to use this measure to chceck the average number of orders per client i various periods and categories

I already have in my model the measure calculating number of orders - MR_NUMBER_OF_ORDERS_WITH_CANCELED - which is distinct count of order_id

Initially I used following DAX fomula:

MR_AVERAGE_NUMBER_OF_ORDERS_PER_CLIENT_DOMAIN_XYZ =
CALCULATE(AVERAGEX(
    VALUES(SALES_EVENTS[EMAIL_ADDRESS]),
    [MR_NUMBER_OF_ORDERS_WITH_CANCELED]),
        FILTER(
SALES_EVENTS, SALES_EVENTS[DOMAIN_NAME] IN {"x.com","y.com","z.com"}))

My data table is very large and visualizations based on this measure have been very slow.

I thought I could improve the speed by using variables in my DAX. I used the following formula: 

MR_AVERAGE_NUMBER_OF_ORDERS_PER_CLIENT_DOMAIN_XYZ_V2 =

var TableOnlyDomainsXYZ = FILTER(SALES_EVENTS, SALES_EVENTS[DOMAIN_NAME] IN {"x.com","y.com","z.com"})

var TableOnlyDomainsXYZClients =
ADDCOLUMNS(SUMMARIZE(TableOnlyDomainsXYZ,SALES_EVENTS[EMAIL_ADDRESS]),
    "NUMBER_OF_ORDERS_WITH_CANCELED",  [MR_NUMBER_OF_ORDERS_WITH_CANCELED])

Return
AVERAGEX(TableOnlyDomainsXYZClients, [NUMBER_OF_ORDERS_WITH_CANCELED])

The new measure works faster but gives slightly different results. It calculates the average only for clients that made purchase via specified domains but includes in the average also the purchases made by these clients via different domains. The first measure calculates the average only for clients that made purchase via specified and includes in the average only the purchases made via these domains - which is what I want to achieve.

Is there a way to change the second measure to get the same results as the first one provides but make it more efficient?

Thank you

[daXtreme · Accepted solution]

// Advice: Please don't use CAPITAL letters in the names of
// objects. Such code hurts readability a lot... and
// the eyes. Thanks.

[Avg Number of Orders Per Domain XYZ] =
var DomainsOfInterest = {
        "x.com",
        "y.com",
        "z.com"
    }
var Output =
    CALCULATE(
        AVERAGEX(
            VALUES( 'Sales Events'[Email Address] ),
            [Number Of Orders With Canceled]
        ),
        KEEPFILTERS(
            'Sales Events'[Domain Name] IN DomainsOfInterest
        )
    )
return
    Output
   
// or something like this...

[Avg Number of Orders Per Domain XYZ v2] =
var DomainsOfInterest = {
        "x.com",
        "y.com",
        "z.com"
    }
VAR Output =
    AVERAGEX(
        CALCULATETABLE(
            DISTINCT( 'Sales Events'[Email Address] ),
            KEEPFILTERS(
                'Sales Events'[Email Address] in DomainsOfInterest
            )
        ),
        [Number Of Orders With Canceled]
     )
return
    Output

[Kaatiiaa]

Thank you!

I guess that the efficiency problem was with using FILTER - when I used KEEPFILTERS instead in my original measure the speed improved a lot even without using variables.

[daXtreme]

@Kaatiiaa 

Please remember not to put an entire table as a filter under CALCULATE. This is an ANTIPATTERN that will always make your code slow like hell due to how the SSAS engine puts filters on columns. The golden rule of DAX is to always filter only what you need and nohthing more. This almost exclusively means filtering columns only.