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

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

4 Replies

  • 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