[SOLVED] Optimize median measure in dax

Optimize median measure in dax

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

Solution

MEDIANX is an iterator and FILTER is another iterator. Also You are iterating the full table('rep vFact') which is not a good practice. I will recommend 3 measures. Please test them and give me feedback.

1st Measure:

Measure_01 =
MEDIANX (
    FILTER ( ALL ( 'rep vFact' ), 'rep vFact'[loanAmount] > 0 ),
    'rep vFact'[loanAmount]
)

2nd Measure

Measure_02 =
MEDIANX (
    CALCULATETABLE ( 'rep vFact', 'rep vFact'[loanAmount] > 0 ),
    'rep vFact'[loanAmount]
)