Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

DAX Counting subscribers by start/end dates taking too long

Hi All,    We have created the following expression;  Subscriber Base = VAR currentDate =MIN ( SubUnsubDateCalendar[Subscription Date] )   RETURN   CALCULATE ( SUM ( Subscriptions[Matc...
Need Help
  • amitchandak's avatar
    amitchandak
    5 years ago

    Anonymous , replace calculate in one of the three  ways

    CALCULATE (
    SUM ( Subscriptions[MatchingRecords] ),
    FILTER (Subscriptions, Subscriptions[Start Date] <= currentDate
    && Subscriptions[End Date] >= currentDate ) ,
    ALLEXCEPT(Subscriptions, Subscriptions[Active], Products[Product Description])
    )

     

    or


    CALCULATE (
    SUM ( Subscriptions[MatchingRecords] ),
    FILTER (Subscriptions, Subscriptions[Start Date] <= currentDate
    && Subscriptions[End Date] >= currentDate ) ,
    ALLEXCEPT(Subscriptions, Subscriptions[Active]),allexpect(Products, Products[Product Description])
    )

     

    or


    CALCULATE (
    SUM ( Subscriptions[MatchingRecords] ),
    FILTER (allselected(Subscriptions), Subscriptions[Start Date] <= currentDate
    && Subscriptions[End Date] >= currentDate && Subscriptions[Active] = max( Subscriptions[Active])) ,
    filter(allselected(Products), Products[Product Description] =max( Products[Product Description]))
    )

    need of allexcept on Subscriptions[Active] ?

amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
5 years ago

Anonymous , replace calculate in one of the three  ways

CALCULATE (
SUM ( Subscriptions[MatchingRecords] ),
FILTER (Subscriptions, Subscriptions[Start Date] <= currentDate
&& Subscriptions[End Date] >= currentDate ) ,
ALLEXCEPT(Subscriptions, Subscriptions[Active], Products[Product Description])
)

 

or


CALCULATE (
SUM ( Subscriptions[MatchingRecords] ),
FILTER (Subscriptions, Subscriptions[Start Date] <= currentDate
&& Subscriptions[End Date] >= currentDate ) ,
ALLEXCEPT(Subscriptions, Subscriptions[Active]),allexpect(Products, Products[Product Description])
)

 

or


CALCULATE (
SUM ( Subscriptions[MatchingRecords] ),
FILTER (allselected(Subscriptions), Subscriptions[Start Date] <= currentDate
&& Subscriptions[End Date] >= currentDate && Subscriptions[Active] = max( Subscriptions[Active])) ,
filter(allselected(Products), Products[Product Description] =max( Products[Product Description]))
)

need of allexcept on Subscriptions[Active] ?

Anonymous's avatar
Anonymous
Not applicable
5 years ago

I'm not sure i do need the allexcept to be honest - it was just in an example i found on here. I think you're right. It's not needed. I'll give those a go and find out 🙂 

 

Thanks, 

Alex