Dax referenced measures causes performance issues

[sivarajan21]

Hi Team,

I have created a dax measure as below:

VT_Targetunitrate_2 =
SUMX ( Accruals, IF ( [Accrual cost source_2] = "Target", [Switch Cost] ) )

I also tried using calculate but i am not able to apply the filter condition because [Accrual cost source_2] is a dax measure

FYI, Accrual Cost Source_2 & Switch cost in above dax code  are both separate measures as below:

[Priority 1],[Priority 2] , [Priority 3] & Parameter Value cost are in turn referenced measures.

But this dax VT_Targetunitrate_2 takes a lot of time to run in visual and its causing performance issues.

PFA file here Financial Management -Tanvi Trial.pbix

Can you please help how to improve performance as this is only a sample file with few million rows. 

Please let me know if you need further info!

Thanks in advance!

@Ahmedx @Anonymous @marcorusso @jgeddes @amitchandak @Anonymous @Greg_Deckler 

[marcorusso · Accepted solution]

Unfortunately, I don't have time to investigate more on your report. 

In general, you could try to rewrite the code without using measure references - it's not the best way, but it is the quickest way to think about how to rewrite the code in a more efficient way. If you are forced to repeat the calculation, probably/hopefully you will find a natural way to write the expression in a simpler way.

[sivarajan21]

Thanks for your response @marcorusso !

For now i will accept it as solution & work on it!

[marcorusso]

You should refactor the calculation trying to reduce the number of context transition. By iterating 26M rows in Accruals, you basically materialize all the tables uncompressed in the formula engine and there is a lot of overhead in the nested calculations. The referenced measures are not an issue by themselves, the context transition is.

I do not have time to investigate deeper, DAX Optimizer can find several issues, but it wouldn't be enough in this case because the solution is not immediate. I suggest that you review the calculation trying to reduce the nested iterators you have now.

[sivarajan21]

Hi @marcorusso  sir,

Thanks for your quick response!

Apologise for the delay in response!

I have been trying to find context transition and nested iterators in my dax code but am struggling.

Can you give me one example from my report on refactoring context transition and nested iterators?

That will help me solve the other and i can take heads from there please?

Thanks in advance!
@marcorusso @Ahmedx @v-linyulu-msft  @jgeddes @amitchandak @v-yaningy-msft @Greg_Deckler