Measure taking too long

[Ackbar-Learner]

Hi

I have a measure which is taking too long to calculate. All measures which precede this delaying measure are ok and do not take much time. See measure below:

Board10d7TaxAmountWithSignActualBudgetActualized = 
VAR Result = [Board10cP&LAllTransactionAmountwithSignBudgetActualized]
VAR TaxApplicable = -1*if([Board10d6TaxCalcCumulativeActualBudgetActualized]>[Board10d5TaxCalcPeriodActualBudgetActualized],[Board10d5TaxCalcPeriodActualBudgetActualized],[Board10d6TaxCalcCumulativeActualBudgetActualized])
RETURN
	calculate(if(if(HASONEVALUE(TabPLGLItem[GLItem]),values(TabPLGLItem[GLItem]))="Tax",TaxApplicable,Result))

Just after this measure, i made another measure to summarize this one.

Board10d8PLTaxAmountWithSignActualBudgetActualized = 
SUMX (
    SUMMARIZE (TabPLGLItem,[GLItem]),
    CALCULATE (
        VAR result = [Board10d7TaxAmountWithSignActualBudgetActualized]
        RETURN
            result
            )
)

Any idea to make this more efficient!

Thanks

[eliasayy · Accepted solution]

Hello @Ackbar-Learner , too many ifs will cause performance issues, your best bet is to put those ifs in a var result = (your if formula) the  return result.

This will boost your performance a bit 

 Try avoiding too many ifs across many measures .what i mean

Measure1 = if(name = value, calc,0)

Then you do another measure

Measure2= if(measure1>0,calc2,calc)

Then you do another measure:

Measure3 = if(measure2 =measure1,calc3,0)

The above is a complete random example to help you understand that this will cause performance issue because poeerbi have to recalculate all the ifs of all measures inside measures.

Putting those measure in var first

Measure1 =

var cond= if(name = value,calc,0)

Return cond

... this might help boost your performance 

[Ackbar-Learner]

@ppm1  @eliasayy  This is an accounting visual that we are trying to build. All the measures are working but only that it is taking far too long as from the measure on tax. In summary, this is what I did:

1. I have a fact table coming from an odata, which contains actual figures. Measure to read Actuals below

Board01aTabPLGL = min(TabPLGLItem[Calc Type])

Board01P&LAllTransactionAmountwithSign = sumx(GLChartOfAccounts,[P&LAllTransactionAmountEUR]*GLChartOfAccounts[Sign])

Board02P&LRunningTotal = if(
     HASONEFILTER(TabPLGLItem[GLItem])
     ,
     CALCULATE(
             [Board01P&LAllTransactionAmountwithSign]
             ,
             ALL(TabPLGLItem[GLItem])
             ,
             TabPLGLItem[Index] <= VALUES(TabPLGLItem[Index])
             )
     ,
     blank()
)

Board03P&LActualAmount = SWITCH(
	[HeaderCalcType]
	,
	BLANK(),BLANK()
	,
	1,[Board01P&LAllTransactionAmountwithSign]
	,
	2,[Board02P&LRunningTotal]
)

2. I have another fact table coming from PowerPivot through Direct Query, which contains budget figures. The DAX code for this is exactly the same as Actuals, except for the naming

3. I then created a P&L containing 2 columns, 1 for Actual & 1 for Budget in 1 matrix visual

4. I now want to create a third column named Actualized Budget. This will add all actual amounts before and upto the date selected and add all budget amounts after the date selected. Measure is given below:

Board10aFirstDateSelected = FIRSTDATE(BudgetCalendar[Date])

Board10aLastDateSelected = LASTDATE(BudgetCalendar[Date])

Board10c1AllTransactionAmountwithSignActualBudgetActualized = 

VAR SeparatorDate = if(and(TODAY()>[Board10aFirstDateSelected], TODAY()> [Board10aLastDateSelected]),[Board10aLastDateSelected],TODAY())

VAR ActualizedActual = CALCULATE([Board01P&LAllTransactionAmountwithSign],filter(BudgetCalendar,BudgetCalendar[Date]<=SeparatorDate))

VAR ActualizedBudget = CALCULATE([AmountWithSign],filter(BudgetCalendar,BudgetCalendar[Date]>SeparatorDate))

RETURN
    ActualizedActual+ActualizedBudget

5. I now need to calculate the actualized Tax based on the actualized budget. The tax formula is that you do not pay any tax up and until your cumulative net losses have not been wiped out; so you need to calculate net profit before tax for both the current financial year and cumulative of all financial years where there is net loss before tax

Net Profit/Loss for current financial year Measures:

Board10d1TaxPeriodActualBudgetActualized = 
VAR Result = 
CALCULATE(
    [Board10c1AllTransactionAmountwithSignActualBudgetActualized],FILTER(all(GLAccountClientUser[GLAccountValue]),GLAccountClientUser[GLAccountValue]<4700000))
RETURN
    Result

Board10d2TaxPeriodRunningTotalActualBudgetActualized = if(
     HASONEFILTER(TabPLGLItem[GLItem])
     ,
     CALCULATE(
             [Board10d1TaxPeriodActualBudgetActualized]
             ,
             ALL(TabPLGLItem[GLItem])
             ,
             TabPLGLItem[Index] < VALUES(TabPLGLItem[Index])
             )
     ,
     blank()
)

Net Profit/Loss for cumulative financial years Measures:

Board10d3TaxCumulativeActualBudgetActualized = 
VAR Result =
CALCULATE([Board10c1AllTransactionAmountwithSignActualBudgetActualized],FILTER(ALL(BudgetCalendar[Date]),BudgetCalendar[Date] >= date(2021,10,1) && BudgetCalendar[Date] <= MAX(BudgetCalendar[Date])),filter(all(GLAccountClientUser[GLAccountValue]),GLAccountClientUser[GLAccountValue]<4700000))
RETURN
    Result

Board10d4TaxCumulativeRunningTotalActualBudgetActualized = if(
     HASONEFILTER(TabPLGLItem[GLItem])
     ,
     CALCULATE(
             [Board10d3TaxCumulativeActualBudgetActualized]
             ,
             ALL(TabPLGLItem[GLItem])
             ,
             TabPLGLItem[Index] < VALUES(TabPLGLItem[Index])
             )
     ,
     blank()
)

Tax Calculation on current financial year Measure:

Board10d5TaxCalcPeriodActualBudgetActualized = 
VAR Result = 
calculate(if([Board10d2TaxPeriodRunningTotalActualBudgetActualized]<0,0,[Board10d2TaxPeriodRunningTotalActualBudgetActualized]*[CorporateTax]))
RETURN
Result

Tax Calculation on cumulative financial years Measure:

Board10d6TaxCalcCumulativeActualBudgetActualized = 
VAR Result = 
calculate(if([Board10d4TaxCumulativeRunningTotalActualBudgetActualized]<0,0,[Board10d4TaxCumulativeRunningTotalActualBudgetActualized]*[CorporateTax]))
RETURN
    Result

6. Measure to get actualized Tax applicable amount in the P&L:

Board10d71TaxAmountWithSignActualBudgetActualized = 
VAR Result = [Board10c1AllTransactionAmountwithSignActualBudgetActualized]
VAR TaxApplicable =
    -1
        * MIN (
            [Board10d5TaxCalcPeriodActualBudgetActualized],
            [Board10d6TaxCalcCumulativeActualBudgetActualized]
        )
RETURN
    IF ( SELECTEDVALUE ( TabPLGLItem[GLItem] ) = "Tax", TaxApplicable, Result )

Measure to summarize above measure:

Board10d81PLTaxAmountWithSignActualBudgetActualized = 
VAR
Result = SUMX (
    SUMMARIZE (TabPLGLItem,[GLItem]),
    CALCULATE (
        [Board10d71TaxAmountWithSignActualBudgetActualized]
    )
)
RETURN
Result

7. Running Total to get Net Profit after Tax

Board11RunningTotalActualBudgetActualized = 
if(
     HASONEFILTER(TabPLGLItem[GLItem])
     ,
     CALCULATE(
             [Board10d81PLTaxAmountWithSignActualBudgetActualized]
             ,
             ALL(TabPLGLItem[GLItem])
             ,
             TabPLGLItem[Index] <= VALUES(TabPLGLItem[Index])
             )
     ,
     blank()
)

8. Lastly, Measure to combine the running totals for net profit with amounts for P&L items:

Board12ProjectedAmountActualBudgetActualized = 
SWITCH(
	[HeaderCalcType]
	,
	BLANK(),BLANK()
	,
	1,[Board10d81PLTaxAmountWithSignActualBudgetActualized]
	,
	2,[Board11RunningTotalActualBudgetActualized]
)

Snapshot of visual after point 4 above. It is after this that performance gets very heavy:

Unfortunately, I cannot share the file, hence such a long post. Hopefully, you or someone out there can make this a bit lighter for better performance 😅

[eliasayy · Accepted solution]

Hello @Ackbar-Learner , too many ifs will cause performance issues, your best bet is to put those ifs in a var result = (your if formula) the  return result.

This will boost your performance a bit 

 Try avoiding too many ifs across many measures .what i mean

Measure1 = if(name = value, calc,0)

Then you do another measure

Measure2= if(measure1>0,calc2,calc)

Then you do another measure:

Measure3 = if(measure2 =measure1,calc3,0)

The above is a complete random example to help you understand that this will cause performance issue because poeerbi have to recalculate all the ifs of all measures inside measures.

Putting those measure in var first

Measure1 =

var cond= if(name = value,calc,0)

Return cond

... this might help boost your performance 

[Ackbar-Learner]

@eliasayy 

Following your reply, i have relooked at the DAX formulas completely and came up with a new set leading to the same results. I think I have been able to optimize where I could and it is still slow but much better than before.

I used Performance Analyzer and understood that I was calculating too much data on the visual and had to filter it way at the start to make it a bit more practical.

Thanks a lot though; this post opened my mind to a lot of new things in Power BI.

[ppm1]

Please try this expression for your first measure instead.

Board10d7TaxAmountWithSignActualBudgetActualized =
VAR Result = [Board10cP&LAllTransactionAmountwithSignBudgetActualized]
VAR TaxApplicable =
    -1
        * MIN (
            [Board10d5TaxCalcPeriodActualBudgetActualized],
            [Board10d6TaxCalcCumulativeActualBudgetActualized]
        )
RETURN
    IF ( SELECTEDVALUE ( TabPLGLItem[GLItem] ) = "Tax", TaxApplicable, Result )

Pat

[Ackbar-Learner]

@ppm1 , thanks for your reply. The code does give me the result but it does not improve the performance. I'll explain the measures leading to this measure in a post just below this and tag you again. Hopefully, you can find something which I could not see!

[ppm1]

Please see my recent video on how to isolate and test each of your measures to see which is the worst performer.  My guess it that it is one or both of the SUMX ones where you have a measure evaluated on each row (1b and 6b).

https://www.youtube.com/watch?v=ScJVQoOWSqc

Pat

[Ackbar-Learner · Accepted solution]

@ppm this is a nice video. I watched the one after and tried to do the same to my model. I won't say it worked perfectly but it is much more improved now.

As for the measure, which is taking a lot of time; it is the sheer amount of data, which is causing this issue. Once i optimized a bit more on the filter, it got much better.

Thanks a lot.

[eliasayy]

hello @Ackbar-Learner if's usually cost performance issue , id suggest putting them into a VAR

Board10d8PLTaxAmountWithSignActualBudgetActualized = 
VAR
Result = SUMX (
    SUMMARIZE (TabPLGLItem,[GLItem]),
    CALCULATE (
        [Board10d7TaxAmountWithSignActualBudgetActualized]
)
RETURN
result

[Ackbar-Learner]

@eliasayy , thanks for your reply. The code does give me the result but it does not improve the performance. I'll explain the measures leading to this measure in a post just below this and tag you again. Hopefully, you can find something which I could not see!