Forum Discussion
Slow Measure
Hello,
Please can someone suggest why the below measure may be hanging? It is in a 'table' visualisation. The first two columns of the table are Employee Name and Customer Name (from dimension tables). The measures [CY YTD Revenue] and [CY YTD Net Profit] are very fast if included as seperate columns so I don't understand why [CY YTD Net Profit (%)] below is so slow.
(there is a fact table with all the transactional data)
CY YTD Net Profit (%) =
if ([CY YTD Revenue] <> 0, [CY YTD Net Profit] / [CY YTD Revenue], sign([CY YTD Net Profit]))
Thank you for any help/pointers,
PW
Hi PowerWhy
to your first question: It runs faster because in your formula you execute two times the same calcultion with
[CY YTD Revenue]your formula acts like this
IF [CY YTD Revenue] is not zero -> the value is calculated and Power BI checks if the value is zero or not
THEN [CY YTD Revenue] -> Power Query executes the calculation again
my formula acts like this
the variable calculates the result of [CY YTD Revenue] -> so Power BI knows the result of [CY YTD Revenue] and "keeps it in mind"
then it does the following:
IF check whether results of variable is zero or not -> Power BI already knows the result so no need to calculate again
THEN results of variable -> Power BI still ahs in mind the result of [CY YTD Revenue] -> so no other calculation
ouy see.. in your version it calculates the same formula two times and in my formula it calculated only once the formula.
regarding your seconfd question I do not understand what you mean to be honest.
Can you please mark thread as solved to help me as well as to help others findin the post when they have the same problem. I also appreciate your thumbs up. 🙂
Best regards
Michael
-----------------------------------------------------
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Appreciate your thumbs up!
@ me in replies or I'll lose your thread.
-----------------------------------------------------
3 Replies
Hi PowerWhy
On edirect potential for optimization is using variables since you do the caluclation of CY YTD REvenue twice
Please try the following:
CY YTD Net Profit (%) = var var_CY_YTD_Revenue = [CY YTD Revenue] RETURN if (var_CY_YTD_Revenue <> 0, [CY YTD Net Profit] / var_CY_YTD_Revenue, sign([CY YTD Net Profit]))By using the variable CY YTD REvenue will only be calculated once and the the result will be used in both cases -> during the IF check and then in the TRUE statement
Another thinkgg you might do on top is the following.
CY YTD Net Profit (%) = var var_CY_YTD_Revenue = [CY YTD Revenue] var var_CY_YTD_NetProfit = [CY YTD Net Profit] RETURN DIVIDE( var_CY_YTD_NetProfit, var_CY_YTD_Revenue, sign(var_CY_YTD_NetProfit) )PowerWhy It might be that you have in the second formula box the wrong version since I had to edit it after I executed the post initially
Best regards
Michael
-----------------------------------------------------
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Appreciate your thumbs up!
@ me in replies or I'll lose your thread.
-----------------------------------------------------
Thank you Mikelytics the first formula worked perfectly 🙂
Please do you know why your formula is so much faster (almost instant) rather than about 50% faster? (given it's replacing 2 parts of a formua with 1?)
Also when I was testing I tried a formula that just returns "1", but this was also hanging - please do you know why that would be?
Hi PowerWhy
to your first question: It runs faster because in your formula you execute two times the same calcultion with
[CY YTD Revenue]your formula acts like this
IF [CY YTD Revenue] is not zero -> the value is calculated and Power BI checks if the value is zero or not
THEN [CY YTD Revenue] -> Power Query executes the calculation again
my formula acts like this
the variable calculates the result of [CY YTD Revenue] -> so Power BI knows the result of [CY YTD Revenue] and "keeps it in mind"
then it does the following:
IF check whether results of variable is zero or not -> Power BI already knows the result so no need to calculate again
THEN results of variable -> Power BI still ahs in mind the result of [CY YTD Revenue] -> so no other calculation
ouy see.. in your version it calculates the same formula two times and in my formula it calculated only once the formula.
regarding your seconfd question I do not understand what you mean to be honest.
Can you please mark thread as solved to help me as well as to help others findin the post when they have the same problem. I also appreciate your thumbs up. 🙂
Best regards
Michael
-----------------------------------------------------
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Appreciate your thumbs up!
@ me in replies or I'll lose your thread.
-----------------------------------------------------
