Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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!
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.
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]?
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])
Check out the July 2025 Power BI update to learn about new features.
User | Count |
---|---|
23 | |
9 | |
7 | |
6 | |
6 |
User | Count |
---|---|
28 | |
11 | |
11 | |
10 | |
6 |