Measure Slow with Added Relationship

[Juliecal73]

Hi,

I feel like I'm missing something fundamental in context here but I just can't figure out what.

I have the following schema with relationships going to a fact table USA Timelisting and a Calendar table.

I am trying to do a simple Matrix visual that is made up of the following fields and measures with a Slicer for USA Accounts 

'USA Projects'[Project]

'USA Tasks'[Task]

#Hrs Pct Used

#Hrs Pct Used Duration

The measures are as follows

#Hrs Pct Used = DIVIDE( SUM( 'USA TimeListing'[Hours] ), SUM( 'USA Tasks'[TaskPlannedHours] ), 0 )

#Hrs Pct Used Duration = 
VAR varTaskStart = MIN('USA Tasks'[Task Planned Start])
VAR varTaskEnd = MAX('USA Tasks'[Task Planned Finish])
VAR varBillableHrsTotal = CALCULATE(SUM('@CalendarOne'[WorkHours]), FILTER( ALLSELECTED( '@CalendarOne'[Date] ), '@CalendarOne'[Date] <= varTaskEnd))
VAR varBillableHrsToDate = CALCULATE(SUM('@CalendarOne'[WorkHours]), FILTER( ALLSELECTED( '@CalendarOne'[Date] ), '@CalendarOne'[Date] <= MIN(TODAY(), varTaskEnd)))

RETURN
DIVIDE(varBillableHrsToDate, varBillableHrsTotal, 0)

Problem:

Scenario 1 combination of fields in the table give a fine performance

Scenario 1 FineScenario 1 Table FineScenario 1 Performance

Scenario 2 combination of fields in the table give a fine performance

Scenario 2 FineScenario 2 Table FineScenario 2 Performance

But when I try and put both Project and Task with the 2 measures, it takes super long. I think there are more iterations happening than are necessary.

Problem ScenarioProblem Scenario TableProblem Performance

Any advice would be much appreciated, this is driving me crazy.

[InTheDark]

@Juliecal73 Did you ever figure out how to fix this? I'm coming across the same thing. 

[Juliecal73]

Will CROSSFILTER help in this scenario?

[mahoneypat]

First thing I would try is to pull the 

MIN(TODAY(), varTaskEnd)

into a variable and use it in the FILTER.

Pat

[Juliecal73]

Thanks @mahoneypat . Yes, neater, but as expected did not make any difference to the performance.

[PaulDBrown]

@Juliecal73 

Can you provide a sample PBIX file (changing any confidential column headings if necessary)?

[Juliecal73]

@PaulDBrown 

Unfortunately it is a big file with other reports in it (haven't got around to seperating them out) so not really possible to provide.

[PaulDBrown]

@Juliecal73 

Ok, I'll let others try to work it out then. Good luck!