cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Resolver I

## Measure taking too long

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

2 ACCEPTED SOLUTIONS
Impactful Individual

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

Resolver I

@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.

9 REPLIES 9
Resolver I

@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(
,
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(
,
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 😅

Impactful Individual

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

Resolver I

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.

Solution Sage

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

Pat

Microsoft Employee
Resolver I

@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!

Solution Sage

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).

Pat

Microsoft Employee
Resolver I

@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.

Impactful Individual

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``````
Resolver I

@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!