Slow Measure

[PowerWhy]

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

[PowerWhy]

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?

[Mikelytics · Accepted solution]

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.

-----------------------------------------------------

LinkedIn

[Mikelytics]

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.

-----------------------------------------------------

LinkedIn