Forum Discussion
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
- amitchandak
Super User
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
- AnonymousNot 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.