The DAX code is asks too much operating memory such that it stays calculating continuously

[Anonymous]

Hi Guys,

I have some DAX issue that the too long to calculate and after 2 hours is failed. 

Column A: Date year
Column B: Amount
Column C: Rate
Column 😧 Amount * Rate

We are trying the translate the following calculations in DAX code:

Result = SumIFS(B(Amount); A(Date Year); ‘>=Year’) / SumIFS(D(Amount*Rate); A(Date Year); ‘>=Year’)

In words:
Weighted rate per year and onwords calculated by: The sum of the amounts of a specific year and after that year divided by the sum of the individual amount times the individual rates of a specific year and after that year

Dax code:

Var A =
CALCULATE (
[EUR Face Value],Filter(ALL ('Dim Trade'),'Dim Trade'[trade_end_year]>= min('Dim Trade'[trade_end_year]))
)

Var B = CALCULATE(AVERAGE('Dim Trade'[funding_spread_bond])* [EUR Face Value],FILTER(ALL('Dim Trade'), 'Dim Trade'[trade_end_year]>= min('Dim Trade'[trade_end_year]))


Return
Cost of funding = [A]/[B]

The DAX code is asks too much operating memory such that it stays calculating continuously. Could someone help with a better/faster/smarter DAX code or other solution.

Thanks in advance!

[v-xiaotang]

Hi @Anonymous 

How large is your dataset? If you only keep a small amount of data, will the measure work properly? 

Best Regards,

Community Support Team _Tang

If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

[tamerj1]

Hi @Anonymous 
you are placing a measure inside the filter!

besides that why are you using AVERAGE? And what is the code for [EUR Face Value]?

[Anonymous]

Hi @tamerj1 ,

Which measure do you mean? Well we need for some reason average as you can see in Excel formule in mine first post. 
This is the formule:

EUR Face Value:= Sum('Fact Trade'[face_value_eur])