Forum Discussion

Markando's avatar
Markando
Frequent Visitor
8 years ago

Performance issue with SumX and Countrows

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)

)

Need Help

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

     

    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.