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/2...
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

     

     

     

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 )