Forum Discussion

Hazenm's avatar
Hazenm
Advocate II
4 years ago
Solved

Non Distinct Count Calculation Efficiency

Have a challenge for folk out there. Maybe there's a much simpler way to do this and I'm not realizing it, but here is the scenario.  I have a table of sales that is right around 1 MM rows. It is ...
  • OwenAuger's avatar
    OwenAuger
    4 years ago

    Hazenm 

    Thanks for testing those out!

     

    Good to get an idea of relative performance, and glad some of them are performing better, but it feels as though performance should be much better!

     

    Period Table filtering

    Looking again at the code you posted earlier to determine the date range to filter, have I understood correctly that 'Period Table' your sole date table?

    I think you could improve performance by rewriting the logic to filter 'Period Table' like this:

     

    VAR curPeriodIndex =
    -- I would generally prefer MAX rather than MIN
    -- Doesn't affect performance but makes more sense if filtering
    -- on multiple Periods.
    MAX ( 'Period Table'[Period Index] )
VAR startPeriod =
    curPeriodIndex - 11
RETURN
    CALCULATE (
        < Some Measure >,
        ALL ( 'Period Table' ),
        'Period Table'[Period Index] >= startPeriod,
        'Period Table'[Period Index] <=curPeriod
    )

     

     

    This version removes filters on 'Period Table' then applies filters to the Period Index column (rather than Date column). The original version using FILTER ( ALL ( 'Period Table' ), ... ) is an iteration over the entire 'Period Table' which can be expensive.

    Would you be able to post a model diagram, or a PBIX with an empty 'All Order Table', and I can generate a fact table at my end?

     

    Repeat Customers calculation itself

    Going back to the different DAX options for Repeat Customers, some ideas occurred to me, that I probably should have thought of earlier!

     

    Version 5

    Uses GENERATE to remove Customers whose first & last order are the same.

    If FirstOrder = LastOrder, then EXCEPT ( FirstOrder, LastOrder ) is empty, and that Customer's row won't appear in result.

     

    Repeat Customers Version 5 =
VAR RepeatCustomers =
    GENERATE (
        VALUES ( 'All Order Table'[Customer] ),
        VAR FirstOrder =
            FIRSTNONBLANK ( 'All Order Table'[OrderNbr], 0 )
        VAR LastOrder =
            LASTNONBLANK ( 'All Order Table'[OrderNbr], 0 )
        RETURN
            EXCEPT ( FirstOrder, LastOrder )
    )
VAR NumRepeatCustomers =
    COUNTROWS ( RepeatCustomers )
RETURN
    NumRepeatCustomers

     

     

    Version 6

    Use HASONEVALUE to see if there is not exactly one OrderNbr for a given Customer. This might be optimised to stop counting when it knows there are 2+ values.

     

    Repeat Customers Version 6 =
VAR RepeatCustomers =
    FILTER (
        VALUES ( 'All Order Table'[Customer] ),
        NOT CALCULATE ( HASONEVALUE ( 'All Order Table'[OrderNbr] ) )
    )
VAR NumRepeatCustomers =
    COUNTROWS ( RepeatCustomers )
RETURN
    NumRepeatCustomers

     

     

    Version 7

    Same as Version 6 but use COUNTROWS (this is really the same logic as Version 3):

     

    Repeat Customers Version 7 =
VAR RepeatCustomers =
    FILTER (
        VALUES ( 'All Order Table'[Customer] ),
        NOT CALCULATE ( COUNTROWS ( 'All Order Table' ) ) = 1
    )
VAR NumRepeatCustomers =
    COUNTROWS ( RepeatCustomers )
RETURN
    NumRepeatCustomers

     

     

    I'm hoping some of this gets us closer to acceptable performance!

     

    Regards,

    Owen

Hazenm's avatar
Hazenm
Advocate II
4 years ago

We took this offline, and OwenAuger was able to get my query down to 6 seconds! 

He created a new measure similar to some of the ones above, but first created a new Customer Table, so that the main calculate argument worked off of that table instead of the order table. This, I think, had the largest impact on performance. 
His key notes on changes; 

    • Some key points that appear to help performance:
  1.       Iterate over the Customer dimension to count customers with one order.
  2.       It’s more efficient to count customers with one order than it is to count customers with 2+ orders.
  •       Filtering on Period Index gave better performance than filtering on Date.
    Also creating the list of periods with GENERATESERIES performed better than using <= and >= conditions.
  1.       Applying the Period Table filter once to the overall calculation, not repeatedly in different parts of the measure.

 

Here was the 'winning' formula: 

12 Month Repurchase Rate Optimized =
VAR EndPeriodIndex =
    MAX ( 'Period Table'[Period Index] )
VAR StartPeriodIndex = EndPeriodIndex – 11
-- Create explicit list of 12 Period Index values
VAR PeriodFilter =
    TREATAS (
        GENERATESERIES ( StartPeriodIndex, EndPeriodIndex ),
        'Period Table'[Period Index]
    )
RETURN
    CALCULATE (
        -- Count Customers with exactly one order
        VAR NumSingleOrderCustomers =
            COUNTROWS (
                FILTER (
                    VALUES ( 'Customer'[Customer] ),
                    // Another option that may perform better if customers are "sparse"
                    -- SUMMARIZE ( 'All Order Table', Customer[Customer] ),
                    CALCULATE (
                        COUNTROWS ( 'All Order Table' )
                    ) = 1
                    // Another option that didn't perform as well
                    -- CALCULATE ( HASONEVALUE ( 'All Order Table'[OrderNbr] ) )
                )
            )
        VAR AllCustomers =
            DISTINCTCOUNT ( 'All Order Table'[Customer] )
        -- Subtract to get NumRepeatCustomers
        VAR NumRepeatCustomers = AllCustomers - NumSingleOrderCustomers
        VAR MMT =
            DIVIDE ( NumRepeatCustomers, AllCustomers )
        RETURN
            MMT,
        -- Apply PeriodFilter to above calculation
        REMOVEFILTERS ( 'Period Table' ),
        PeriodFilter
    )


HUGE kudos to OwenAuger ! Thanks a lot!