Performance issue with SumX and Countrows

[Markando]

Hi everyone

I've got some performance issues with an sumx and countrows.

My missionTasks table contains of about 4500 rows. My goal is to calculate how many workingdays has ackumulated between [Task start date] and today in a linegraph (I use that further on in another equation). 

So for every day in the graph (600 days) I performe the calculation below for 4500 rows and then add the total up. So I understand why it's so slow, but it would be intresting to see if you DAX-gurus has any input on how to make this more effective

SUMX(MissionTasks;
CALCULATE(
COUNTROWS(Calender);
FILTER(ALL(Calender[Date]);
Calender[Date]>=MissionTasks[Task start date]&&
Calender[Date]<=MAX(Calender[Date])&&
Calender[Date]<=MissionTasks[Master_Task due date])

;

Calender[Weekday_Calender]<=5)

)

[Anonymous]

I would split this into 2 different measures and complete it like this:

Firstly create:

Date Difference Count = VAR StartDate = FIRSTDATE('Calendar'[Date])
Var EndDate = NOW()
RETURN 
CALCULATE(
    countrows('Calendar'),
    ALL('Calendar'),
    'Calendar'[Date] >= StartDate,
    'Calendar'[Date] <= EndDate,
    Calender[Weekday_Calender] <= 5
)

Then you need:

Your Measure = SUMX(
    Values('MissionTasks'[Task Start Date]),
    [Date Difference Count]
)

You may need to set a filter to ignore any Blank [Task Start Date] values in your filters.  I created a quick simple dataset and needed to do that.  If you dont, thats ok.

[Greg_Deckler]

Can you post some example/sample data?

Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490