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

Hey Owen! Thanks for replying so quickly!
Here are my comments: 
The use of period table = we work on a 4-4-5 Fiscal Period Cal that starts in July. The period table is a reference/calendar table that stores all of the company fiscal weeks, periods, and years.  Yes, one row per day. 

"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.)" 
-Yes, this is how this is built, and the relationship is setup. However, because the chart is broken out into periods, each period, because of the relationship, is filtered to that period. 
For example, the X-Axis point at Period 9 of FY22, "202209" is filtering period table (and thus order date table) to only dates in 202209. This means I have to use something like DATESBETWEEN to remove that filter contex, and instead apply the new filter criteria which is defined by the variables you mentioned: 

VAR curPeriodIndex = MIN('Period Table'[Period Index])
(Period index is calculated column on the period table labeling each period incrementally from the first. This saves the virtual tables from having to create some kind of index of periods to subtract the desired number from on the fly. Some time these moving totals are not a full year, but a number of periods. So good for me to have this flexibility pre-built)
VAR startPeriod = curPeriodIndex - 11
VAR endOfPeriod = MAX('Period Table'[Day])
VAR startOfPeriod =
MINX(
   FILTER(
      ALL('Period Table'),
         'Period Table'[Period Index] = startPeriod
      ),
      'Period Table'[Day]
   )

)

So it's finding first day in the period 11 months prior, and last day of current period to get the 12 month range. 
Again, the "DATESBETWEEN" is removing our 'row context' of 202209 (dates only occurrring in FY 2022 Period 9), and applying the new context of all dates in last 12 months. 

 

Okay, so those notes aside, I attempted the variation you supplied, and I'm still getting a 15 second load time. I do appreciate what you've done here though! Counting rows vs counting records, always better. I didn't understand that applying "CALCULATE" to the start of the new summarized column would apply the row context back to the COUNTROWS function. Brilliant! Also appreciate the minor tweak of using DIVIDE instead of "/" - I always forget to do that.

This did give me an idea to attempt removing filter context from PERIOD TABLE completely, then applying filter directly to the Order Table. But that didn't really improve efficiency at all. 

 

 

  • OwenAuger's avatar
    OwenAuger
    Super User
    4 years ago

    Hi again Hazenm 

    Thanks for testing it out, and providing further explanation of how your data model is set up.

     

    It looks like my suggestions haven't addressed the core performance issue.

     

    I think if we can focus on the calculation of "number of repeat customers" in isolation, that might help get to the bottom of the issue. I think the Period/Date filtering is probably not hugely impacting performance.

     

    I've come up with some "Repeat Customers" measures to test below, as this seems to be the expensive part of the calculation.

     

    Could you create the below measures and see how they perform, either in a test visual grouped by Year/Month or some other dimension(s)?

    I was using DAX Studio myself to test these in a sample model, so you could also do something similar.

     

    Interested to hear how performance is with these, and whether any of them is an improvement!

     

    Regards,

    Owen

     

    Repeat Customers Version 1

    Repeat Customers Version 1 =
// SUMMARIZE by Customer/OrderNbr
VAR CustomerOrder =
    SUMMARIZE (
        'All Order Table',
        'All Order Table'[Customer],
        'All Order Table'[OrderNbr]
    )
// GROUPBY to count orders per customer
VAR CustomerOrderCount =
    GROUPBY (
        CustomerOrder,
        'All Order Table'[Customer],
        "@Orders", SUMX ( CURRENTGROUP (), 1 )
    )
VAR RepeatCustomers =
    FILTER ( CustomerOrderCount, [@Orders] > 1 )
VAR NumRepeatCustomers =
    COUNTROWS ( RepeatCustomers )
RETURN
    NumRepeatCustomers

     

    Repeat Customers Version 2

    Repeat Customers Version 2 =
// ADDCOLUMNS / SUMMARIZE / DISTINCTCOUNT to get orders per customer
VAR CustomerOrderCount =
    ADDCOLUMNS (
        SUMMARIZE ( 'All Order Table', 'All Order Table'[Customer] ),
        "@Orders", CALCULATE ( DISTINCTCOUNT ( 'All Order Table'[OrderNbr] ) )
    )
VAR RepeatCustomers =
    FILTER ( CustomerOrderCount, [@Orders] > 1 )
VAR NumRepeatCustomers =
    COUNTROWS ( RepeatCustomers )
RETURN
    NumRepeatCustomers

     

    Repeat Customers Version 3

     

    Repeat Customers Version 3 =
// ADDCOLUMNS / SUMMARIZE / COUNTROWS to get orders per customer
VAR CustomerOrderCount =
    ADDCOLUMNS (
        SUMMARIZE ( 'All Order Table', 'All Order Table'[Customer] ),
        "@Orders", CALCULATE ( COUNTROWS ( 'All Order Table' ) )
    )
VAR RepeatCustomers =
    FILTER ( CustomerOrderCount, [@Orders] > 1 )
VAR NumRepeatCustomers =
    COUNTROWS ( RepeatCustomers )
RETURN
    NumRepeatCustomers

     

     

    Repeat Customers Version 4

    (a bit of an experiment - don't expect to perform too well)

    Repeat Customers Version 4 =
// SUMMARIZE by Customer/OrderNbr
VAR CustomerOrder =
    SUMMARIZE (
        'All Order Table',
        'All Order Table'[Customer],
        'All Order Table'[OrderNbr]
    )
// Find First Order for each Customer
VAR CustomerFirstOrder =
    GENERATE (
        VALUES ( 'All Order Table'[Customer] ),
        FIRSTNONBLANK ( 'All Order Table'[OrderNbr], 0 )
    )
// Take set difference between CustomerOrder & CustomerFirstOrder
VAR CustomerExceptFirstOrder =
    EXCEPT ( CustomerOrder, CustomerFirstOrder )
VAR RepeatCustomers =
    SUMMARIZE ( CustomerExceptFirstOrder, 'All Order Table'[Customer] )
VAR NumRepeatCustomers =
    COUNTROWS ( RepeatCustomers )
RETURN
    NumRepeatCustomers

     

    • Hazenm's avatar
      Hazenm
      Advocate II
      4 years ago

      Owen!
      Thanks so much for putting these together! This was really nice!
      Here are the results: 
      First off, I had to add the DATESBETWEEN function to each of these variations to allow them to calc the correct number. 

      Version One: 
      This was the best one. After applying the datesbetween function, it took 9 seconds originally. I made some minor tweaks to try to reduce the load, but I couldn't get it much below 9. For example, the original summarize is not required, because the order table is already one row per order. So I changed the summarize to just: 

      CALCULATETABLE(
         'All Order Table',
         DATESBETWEEN('Period Table'[Day],startOfPeriod,endOfPeriod)
      )

      I thought this would reduce performance further, but no change. I think there might be a way here to get it down, but already, this is much improved from last results. 

      VERSION TWO: 
      After applying datesbetween, this one took just under 19 seconds

       

      VERSION THREE: 

      Slightly better performance on this variation, at 13 seconds.


      VERSION FOUR: 
      I love the creativity in this process. This is what I was trying to come up with when I was originally trying to develop some way to create a NON-DISTINCT formula. 
      Unfortunately, as you predicted, this took longer, at 23 seconds. 
      I even attempted to play with it a little bit to make it faster. The GENERATE table part of the formula was taking quite abit, so I changed it to this: 

      CALCULATETABLE(
         ADDCOLUMNS(
            VALUES('All Order Table'[Customer]),
            "OrderNbr",
            CALCULATE(FIRSTNONBLANK('All Order Table'[OrderNbr],0))
         ),
         DATESBETWEEN('Period Table'[Day],startOfPeriod,endOfPeriod)
      )
      Interestingly, it was slightly faster on its own, but then applied back to the EXCEPT and SUMMARIZE and COUNTROWS, and it took much longer than the initial calculation. 

      My gut tells me this direction is good, and there is some way to massively simplify this idea, but I can't think of what it is. Distinct values of first ordernbr and and EXCEPT and another distinct count of customer.  But maybe this direction is just always going to take more steps. Is there some other function that gets at this via a shorter path that we're not considering? 

      So it's either some major tweakage with the last idea, or some further improvements, potentially on the first variation. 
      At this point, with the 9 second calculation, I could probably release this and it'll be fine, but I wanted to add a few more variables on the chart that would have added more load time. 
      But I also want to see if this performance issue can be cracked!
      • OwenAuger's avatar
        OwenAuger
        Super User
        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