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

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