topic Re: Measure taking too long in DAX Commands and Tips

Measure taking too long

Ackbar-Learner — Wed, 18 Jan 2023 19:57:12 GMT

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

Re: Measure taking too long

Anonymous — Thu, 19 Jan 2023 00:14:30 GMT

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

Re: Measure taking too long

ppm1 — Thu, 19 Jan 2023 00:39:42 GMT

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

Re: Measure taking too long

Ackbar-Learner — Thu, 19 Jan 2023 14:45:49 GMT

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!

Re: Measure taking too long

Ackbar-Learner — Thu, 19 Jan 2023 14:46:17 GMT

Anonymous , 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!

Re: Measure taking too long

Ackbar-Learner — Thu, 19 Jan 2023 18:32:17 GMT

ppm1 Anonymous 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 😅

Re: Measure taking too long

ppm1 — Fri, 20 Jan 2023 00:11:03 GMT

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

Re: Measure taking too long

Anonymous — Sat, 21 Jan 2023 04:58:48 GMT

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

Re: Measure taking too long

Ackbar-Learner — Sun, 22 Jan 2023 10:00:29 GMT

Anonymous

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.

Re: Measure taking too long

Ackbar-Learner — Sun, 22 Jan 2023 10:03:08 GMT

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