Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Very slow measures

Hi all, 

I've been trying to get 2 measures to work more efficiently, and I can't seem to pin down what the problem comes from after hours analyzing it : 

 

ActiveMembers :=
CALCULATE (
    [IsMember],
    FILTER (
        VALUES ( 'Client'[Last order] ),
        LASTDATE ( Client[Last Order] )
            > LASTDATE ( 'Time'[Date] ) - 112
            && LASTDATE ( 'Client'[Last order] ) <= LASTDATE ( 'Time'[Date] )
    )
)
DormantMembers :=
IF (
    HASONEVALUE ( 'Client'[Last Order] ),
    CALCULATE (
        [IsMember],
        FILTER (
            ALL ( 'Time'[Week] ),
            LASTDATE ( Client[Last Order] )
                <= LASTDATE ( 'Time'[Date] ) - 112
        )
    ),
    CALCULATE (
        [IsMember],
        FILTER (
            VALUES ( Client[Last Order] ),
            LASTDATE ( Client[Last Order] )
                <= LASTDATE ( 'Time'[Date] ) - 112
        )
    )
)

 

 

For reference, IsMember is : 

 

IsMember :=
CALCULATE ( DISTINCTCOUNT ( [ID] ), Facts[Member] = 1 )

 

 

Do you have an idea what could be the problem here ? I'm trying to see if I can work upstream and calculate this with an SSIS query instead, but it'd be better to fix it from DAX directly.

 

Kindly,

Nossair

2 Replies

  • Anonymous , if are creating a rolling 112 days formula then try like

     

    Rolling 112 = CALCULATE([Is Member],DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-112,day))

     

    Can you explain what are you trying to achieve here

    • Anonymous's avatar
      Anonymous
      Not applicable

      Hi amitchandak,

      The first measures calculates the number of clients that have made a purchase in the last 16 weeks, and the second calculates the number of clients whose last purchase came before that.