DAX Counting subscribers by start/end dates taking too long

[Alex1988]

Hi All, 

We have created the following expression; 

Subscriber Base =

VAR currentDate =MIN ( SubUnsubDateCalendar[Subscription Date] )

RETURN

CALCULATE (

SUM ( Subscriptions[MatchingRecords] ),

FILTER (

ALLEXCEPT(Subscriptions, Subscriptions[Active], Products[Product Description]),

( Subscriptions[Start Date] <= currentDate

&& Subscriptions[End Date] >= currentDate )))

The table it comes from is a aggregated table from sproc that groups the subscribers into common start/end date combinations;

[Product], [StartDate],[EndDate],[SubscriptionCount] (There are other fields but this is the granularity)

e.g.

ProductA, 01/01/2021 00:00:00,  25/01/2021 00:00:00, 25

My guess is it is taking time to count all the rows (as there are a very large number of records) and needs to assess dates and times for each. Is there a better way of doing this or should I just handle this in a SQL Stored procedure before the dataset?

Thanks from a beginner!, 

Alex

[amitchandak]

@Alex1988 , if you trying to find active subscriptions, refer to a similar blog

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

or

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

[Alex1988]

This is the table from the datset with a relationship to a date calendar on startdate(active) and EndDate(inactive) and some relationships to other dimensions like products etc.

The Dax expression provided gives me: 

Which is what i want - it's calculating base the way i would hope. It's just that when i select a product it takes ten minutes to recaculate becase there are lots of rows and dates to be computed. 

[v-robertq-msft]

Hi, @Alex1988 

According to your DAX formula and output chart, it seems like that you have found a solution to achieve this in Power BI, right?

If you want to reduce the calculation time of Power BI based on such a big dataset, I think you can try to disable the useless columns in the Power Query.

If you handle this in a SQL Stored procedure before the dataset, I don’t think this will give your query speed a qualitative increase because the large dataset will also take plenty of time to be queryed.

If you still have a problem, you can post some sample data(without sensitive data) and your expected result.

How to Get Your Question Answered Quickly 

Best Regards,

Community Support Team _Robert Qin

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak · Accepted solution]

@Alex1988 , 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] ?

[Alex1988]

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