Forum Discussion

AlB's avatar
AlB
Community Champion
5 years ago
Solved

Optimizing measure - Segment migration

Hi all,

See the attached file for support.

We have a simple one-table model:

CustomerId Dates Segment
6 03/01/2020 Segment2
7 03/01/2020 Segment2
6 03/02/2020 Segment1
7 03/02/2020 Segment2
6 03/03/2020 Segment1
7 03/03/2020 Segment1

 

with info on what segment customers belonged to at specific dates. We build a matrix visual with Segment in rows, Dates in columns and this measure in values:

 

Net new customers = 
VAR _CurrentDate =
    SELECTEDVALUE (Table1[Dates] )
VAR _PreviousDate =
    CALCULATE (
        MAX ( Table1[Dates] ),
        ALLSELECTED ( Table1[Dates] ),
        Table1[Dates] < _CurrentDate
    )
VAR _CustomerIDsThisMonth = DISTINCT(Table1[CustomerId]) 
VAR _CustomerIDsPreviousMonth =
    CALCULATETABLE (
        DISTINCT(Table1[CustomerId]),
        Table1[Dates] = _PreviousDate
    )
RETURN
    COUNTROWS(EXCEPT(_CustomerIDsThisMonth, _CustomerIDsPreviousMonth))

 

This provides the number of new customers that moved to that segment at that date (from the previous date).

This works fine for low number of customers. When that increases, however, it gets quite slow. For instance, with 1 million customers and info on 10 dates in the table, the measure takes around 13 secs to execute in the visual. If the number of customers and dates is higher, like in the real scenario, it is much slower still.

Any ideas on how to optimize this, make it faster?

Note that you can change the size of the table by changing the number of customers and number of periods (dates) in the query editor. Simply edit the parameters NumCustomers and NumPeriods.

mahoneypat , MFelix TomMartens 

 

 

Show and Tell
  • mwegener's avatar
    mwegener
    5 years ago

     

    Hi AlB ,

     

    try this.

    Net new customers v2 = 
VAR _CurrentDate =
    SELECTEDVALUE (Table1[Dates] )
VAR _PreviousDate =
    CALCULATE (
        MAX ( Table1[Dates] ),
        ALLSELECTED ( Table1[Dates] ),
        Table1[Dates] < _CurrentDate
    )
VAR _CountCustomerIDsThisMonth =
    CALCULATE( 
        DISTINCTCOUNT(Table1[CustomerId]),
        Table1[Dates] = _PreviousDate ||
        Table1[Dates] = _CurrentDate
    )
VAR _CountCustomerIDsPreviousMonth =
    CALCULATE( 
        DISTINCTCOUNT(Table1[CustomerId]),
        Table1[Dates] = _PreviousDate
    )
RETURN
    _CountCustomerIDsThisMonth - _CountCustomerIDsPreviousMonth

     

     

     

11 Replies

  • mwegener's avatar
    mwegener
    Most Valuable Professional
    5 years ago

     

    Hi AlB ,

     

    try this.

    Net new customers v2 = 
VAR _CurrentDate =
    SELECTEDVALUE (Table1[Dates] )
VAR _PreviousDate =
    CALCULATE (
        MAX ( Table1[Dates] ),
        ALLSELECTED ( Table1[Dates] ),
        Table1[Dates] < _CurrentDate
    )
VAR _CountCustomerIDsThisMonth =
    CALCULATE( 
        DISTINCTCOUNT(Table1[CustomerId]),
        Table1[Dates] = _PreviousDate ||
        Table1[Dates] = _CurrentDate
    )
VAR _CountCustomerIDsPreviousMonth =
    CALCULATE( 
        DISTINCTCOUNT(Table1[CustomerId]),
        Table1[Dates] = _PreviousDate
    )
RETURN
    _CountCustomerIDsThisMonth - _CountCustomerIDsPreviousMonth

     

     

     

    • mwegener's avatar
      mwegener
      Most Valuable Professional
      5 years ago

      HI AlB ,

       

      depending on the underlying data, this adjustment could also help.

      Net new customers v3 = 
VAR _CurrentDate =
    SELECTEDVALUE (Table1[Dates] )
VAR _PreviousDate =
    CALCULATE (
        MAX ( Table1[Dates] ),
        ALLSELECTED ( Table1[Dates] ),
        Table1[Dates] < _CurrentDate
    )
VAR _CountCustomerIDsThisMonth =
    CALCULATE( 
        SUMX(VALUES(Table1[CustomerId]),1),
        Table1[Dates] = _PreviousDate ||
        Table1[Dates] = _CurrentDate
    )
VAR _CountCustomerIDsPreviousMonth =
    CALCULATE( 
        SUMX(VALUES(Table1[CustomerId]),1),
        Table1[Dates] = _PreviousDate
    )
RETURN
    _CountCustomerIDsThisMonth - _CountCustomerIDsPreviousMonth

       

      Got it from the Guy in a Cube video "Debugging a slow Power BI report with Phil Seamark"

      https://www.youtube.com/watch?v=eABg872TAJU

       

       

  • AlB's avatar
    AlB
    Community Champion
    5 years ago

    Hi mwegener 

    Many thanks for your responses. How didn't I think of this approach 🙄 ?? V2 is way way faster. Great!

    Interestingly enough, though, V3 is slower than the original one ğŸ¤” which I wouldn't expect seeing V2's performance 

     

     

  • AlB's avatar
    AlB
    Community Champion
    5 years ago

    mwegener  (and everyone else)

    Allow me to challenge you a bit further. See attached file for support.

    We now want to calculate the revenue (sales) that the new customers that moved to the segment have generated. For that we have added an extra column (Table1[Sales]). We then have an initial approach, similar to [Net new customers]. Very slow as well:

     

    Net new customers purchases = 
VAR _CurrentDate = SELECTEDVALUE ( Table1[Dates] )
VAR _PreviousDate = CALCULATE ( MAX ( Table1[Dates] ), Table1[Dates] < _CurrentDate )
VAR _CustomerIDsThisMonth = DISTINCT ( Table1[CustomerId] )
VAR _CustomerIDsPreviousMonth =
    CALCULATETABLE (
        DISTINCT ( Table1[CustomerId] ),
        Table1[Dates] = _PreviousDate
    )
RETURN
    CALCULATE (
        SUM ( Table1[Sales] ),
        EXCEPT ( _CustomerIDsThisMonth, _CustomerIDsPreviousMonth )
    )

     

    Following an approach similar to mwegener 's V2 (although it cannot be followed completely in this case), we can make it twice as fast, but still relatively slow:

     

    Net new customers purchases V2 = 
VAR _CurrentDate = SELECTEDVALUE ( Table1[Dates] )
VAR _PreviousDate = CALCULATE ( MAX ( Table1[Dates] ), Table1[Dates] < _CurrentDate )
VAR _CustomerIDsThisMonth =
    CALCULATETABLE (
        DISTINCT ( Table1[CustomerId] ),
        Table1[Dates] IN  {_PreviousDate , _CurrentDate}
    )
VAR _CustomerIDsPreviousMonth =
    CALCULATETABLE (
        DISTINCT ( Table1[CustomerId] ),
        Table1[Dates] = _PreviousDate
    )
RETURN
    CALCULATE ( SUM ( Table1[Sales] ), _CustomerIDsThisMonth )
        - CALCULATE ( SUM ( Table1[Sales] ), _CustomerIDsPreviousMonth )

     

    Can you make it faster?

    Many thanks

    • mwegener's avatar
      mwegener
      Most Valuable Professional
      5 years ago

      Hi AlB ,

      try this.

       

      Net new customers purchases V4 = 
VAR _CurrentDate =
    SELECTEDVALUE ( Table1[Dates] )
VAR _PreviousDate =
    CALCULATE ( MAX ( Table1[Dates] ), Table1[Dates] < _CurrentDate )
RETURN
    SUMX (
        Table1,
        IF (
            CALCULATE (
                COUNTROWS ( FILTER ( Table1, Table1[Dates] = _PreviousDate ) ),
                REMOVEFILTERS ( Table1[Dates] )
            ) >= 1,
            BLANK (),
            Table1[Sales]
        )
    )

       

       

       

  • AlB's avatar
    AlB
    Community Champion
    5 years ago

    mwegener 

    I'm primarily interested in a DAX solution but if it cannot be optimized further in DAX PQ is an option too

     

  • AlB's avatar
    AlB
    Community Champion
    5 years ago

    mwegener 

    Thanks for V4. Smart alternative.  It doesn't seem to run faster, I'm  afraid. These are the readings in DAX Studio with 1M customers:

    - [ Net new customers purchases]:     22.5 secs   

    - [ Net new customers purchases V2 ] : 25.7 secs

    - [ Net new customers purchases V4 ]:  23.7 secs

     

    So no massive differences. V4 actually introduces a Callback Data at the end but that doesn't seem to have a big effect. Earlier I claimed [ Net new customers purchases V2 ] was twice as fast as  [ Net new customers purchases] but i cannot reproduce that now. Perhaps I made a mistake measuring, it was with the Performance analyzer with PBI. Dax Studio should be more reliable

    Thanks

     

     

     

     

     

    • mwegener's avatar
      mwegener
      Most Valuable Professional
      5 years ago

      Hi AlB ,

       

      try this.

      Net new customers purchases V5 =
VAR _CurrentDate =
    SELECTEDVALUE ( Table1[Dates] )
VAR _PreviousDate =
    CALCULATE ( MAX ( Table1[Dates] ), Table1[Dates] < _CurrentDate )
VAR _NewCustomer =
    FILTER (
        VALUES ( Table1[CustomerId] ),
        VAR _CountPreviousCustomer =
            CALCULATE ( COUNT ( Table1[CustomerId] ), Table1[Dates] = _PreviousDate )
        VAR _IsInPreviousDate =
            _CountPreviousCustomer = BLANK ()
        RETURN
            _IsInPreviousDate
    )
RETURN
    CALCULATE ( SUM ( Table1[Sales] ), _NewCustomer )
  • AlB's avatar
    AlB
    Community Champion
    5 years ago

    mwegener 

    Thanks so much for your continued efforts. V5 shows a bit of an improvement on 1M customers, but not really significant.

    - [ Net new customers purchases V5]: 21.5 secs

    Perhaps we've reached the optimal approach without using PQ or changing the model?

    Thanks