CODEWITHSUNDEEP
powerbidax
Jay Desai
Jay Desai

Reputation: 861

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.

enter image description here

When I filter on ReportPath it shows wrong values.

enter image description here

Expected result should be as below:

enter image description here

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.

Upvotes: 1

Views: 4752

Answers (1)

Alexis Olson
Alexis Olson

Reputation: 40204

Try this measure:

AveExecTime = CALCULATE(AVERAGE(LongRR[TotalTime]),
                  FILTER(ALLSELECTED(LongRR),
                      LongRR[ReportName] IN VALUES(LongRR[ReportName])))

The ALLSELECTED function preserves the slicer setting and then we say the ReportName must be in the current filter context (equal to the value you have in that table row).

Upvotes: 1

Related Questions