ALLEXCEPT function does not work with filter

[jaydesai28]

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]),ALLEXCEPT('Long Running Report','Long Running Report'[ReportName]))

Data has one column called ReportPath which I am using as filter. when I don't filter on ReportPath measure shows correct average value per report.

 

When I filter on ReportPath it shows wrong values.

I know this is happening because of ALLEXCEPT(ReportName) but is there any way to include filter as well. There could be more than one filter on dashboard in any case it should display correct average. 

I am using DirectQueryMode cannot use FILTER.

[v-jiascu-msft]

Hi @jaydesai28,

Could you please mark the proper answer as a solution?

Best Regards,
Dale

[v-jiascu-msft]

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]

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

[v-jiascu-msft · Accepted solution]

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]
    )
)

Best Regards,

Dale