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...
Need Help
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.