Forum Discussion

Juliecal73's avatar
Juliecal73
Helper III
5 years ago

Measure Slow with Added Relationship

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.

Need Help

7 Replies

    • Juliecal73's avatar
      Juliecal73
      Helper III
      5 years ago

      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.

  • mahoneypat's avatar
    mahoneypat
    Microsoft Employee
    5 years ago

    First thing I would try is to pull the 

    MIN(TODAY(), varTaskEnd)

    into a variable and use it in the FILTER.

     

    Pat