Optimize median measure in dax

[Anonymous]

I have the below median measure that I am using in my report, but when I publish to power bi service, I get this error message "Resources Exceeded This visual has exceeded the available resources. Try filtering to decrease the amount of data displayed". Resource Governing: This query uses more memory than the configured limit. The query — or calculations referenced by it — might be too memory-intensive to run. Either reach out to your Analysis Services server administrator to increase the per-query memory limit or optimize the query so it consumes less memory. More details: consumed memory 11753 MB, memory limit 10240 MB."

I have increase the capacity in both AAS and Power BI but still getting same error.

The measure is

CALCULATE ( MEDIANX( FILTER('rep vFact', 'rep vFact'[loanAmount] > 0), ( 'rep vFact'[loanAmount])) )

Please, how do I optimze this measure to be more efficient. Thanks

[v-binbinyu-msft · Accepted solution]

Hi @Anonymous ,

You can try to Optimize your model to increase the performance.

For more details, you can read related document:Optimize a model for performance in Power BI  - Learn | Microsoft Docs

Best regards,
Community Support Team_ Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

@bcdobbs , thanks for your response. I will check out the link you provided

[bcdobbs]

As a starting point you could try:

CALCULATE ( 
	MEDIAN( 'rep vFact'[loanAmount] )
	'rep vFact'[loanAmount] > 0
)

Also make sure the Loan Amount column is set to Decimal/Currency.

Not convinced above will work though. Will give it some more thought.

[bcdobbs]

There is a limit on using MEDIAN on a table of 2 billion plus rows and suggested work around is the pattern in:

https://www.daxpatterns.com/statistical-patterns/#median