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

OwenAuger's avatar
OwenAuger
Super User
4 years ago

Hi Hazenm 

The logic of the calcuation looks good, but there should be room to improve performance here.

 

  • First of all: the use of 'Period Table' seems unusual - is it a Date table with one row per date, and does it have a relationship with 'All Orders Table'?
    In your code above, it is not clear where startOfPeriod and endOfPeriod are defined - where do they come from? I'm assuming they are the bounds of the 12 month period.
    Ideally, you should have a Date table containing one row per date, with a relationship with 'All Orders Table'. If set up correctly, this will remove the need to construct a date filter using DATESBWEEN, and hopefully improve performance. (There could be something I'm missing here though.)
  • Leaving the 'Period Table' as-is for now, and assuming startOfPeriod & endOfPeriod are defined somehow, we can improve performance by minimizing the number operations on 'All Order Table', and using since we want a row count, COUNTROWS is preferable to COUNT of a particular column. We still need to user FILTER to identify repeat customers - I can't really see any alternative to that.

 

 

Repurchase Rate =
VAR CustomerNumOrders =
    CALCULATETABLE (
        ADDCOLUMNS (
            SUMMARIZE ( 'All Order Table', 'All Order Table'[Customer] ),
            "@NumOrders", CALCULATE ( COUNTROWS ( 'All Order Table' ) )
        ),
        DATESBETWEEN ( 'Period Table'[Day], startOfPeriod, endOfPeriod )
    )
VAR Customers =
    COUNTROWS ( CustomerNumOrders )
VAR RepeatCustomers =
    COUNTROWS ( FILTER ( CustomerNumOrders, [@NumOrders] > 1 ) )
VAR MMT =
    DIVIDE ( RepeatCustomers, Customers )
RETURN
    MMT

 

 

 Does something like this work, and how is performance?

 

Regards,

Owen