Forum Discussion

jaydesai28's avatar
jaydesai28
Frequent Visitor
8 years ago
Solved

ALLEXCEPT function does not work with filter

I want to calculate the average report execution time per report. I created measure for average execution time.   AverageExecutionTime = CALCULATE(AVERAGE('Long Running Report'[TotalTime]),ALLEXCEP...
Need Help
  • v-jiascu-msft's avatar
    v-jiascu-msft
    8 years ago

    Hi jaydesai28,

     

    Based on your data here, you can just remove the context of "TotalTime". 

    AverageExecutionTime =
CALCULATE (
    AVERAGE ( 'Long Running Report'[TotalTime] ),
    ALL ( 'Long Running Report'[TotalTime] )
)

    Or you can add the context of ReportPath to your formula.

    AverageExecutionTime 2 =
CALCULATE (
    AVERAGE ( 'Long Running Report'[TotalTime] ),
    ALLEXCEPT (
        'Long Running Report',
        'Long Running Report'[ReportName],
        'Long Running Report'[ReportPath]
    )
)

    ALLEXCEPT_function_does_not_work_with_filter

     

     

    Best Regards,

    Dale

v-jiascu-msft's avatar
v-jiascu-msft
Microsoft Employee
8 years ago

Hi jaydesai28,

 

Try out the function ALLSELECTED like below.

AverageExecutionTime =
CALCULATE (
    AVERAGE ( 'Long Running Report'[TotalTime] ),
    ALLSELECTED ( 'Long Running Report'[ReportName] )
)

Best Regards,

Dale

jaydesai28's avatar
jaydesai28
Frequent Visitor
8 years ago

It does not show correct average time. It's a replication of TotalTime.

  • v-jiascu-msft's avatar
    v-jiascu-msft
    Microsoft Employee
    8 years ago

    Hi jaydesai28,

     

    Based on your data here, you can just remove the context of "TotalTime". 

    AverageExecutionTime =
CALCULATE (
    AVERAGE ( 'Long Running Report'[TotalTime] ),
    ALL ( 'Long Running Report'[TotalTime] )
)

    Or you can add the context of ReportPath to your formula.

    AverageExecutionTime 2 =
CALCULATE (
    AVERAGE ( 'Long Running Report'[TotalTime] ),
    ALLEXCEPT (
        'Long Running Report',
        'Long Running Report'[ReportName],
        'Long Running Report'[ReportPath]
    )
)

    ALLEXCEPT_function_does_not_work_with_filter

     

     

    Best Regards,

    Dale