Dax Optimisation

[Krishna_Mysore]

Hi Guys

Seeking help to optimise the below Dax Function . I have tried to figure it out myself using DAX studio but in vain.

I have a Report with multiple pages showing Income Statement in Summary as well as Detailed which compares Actual Vs (Budget) and Last Year.

Below is the Measure for calculating Actuals and have similar measure for Budget and Last Year.  There results are show in a Matrix Table with variances.  It takes around 9 seconds to just load "Income Statement" summary page and when clicked on Break-up Page, the response time around 50 ~ 60 seconds.

Any guidance to optimise the measure is highly appreciated.

Thanks

Krishna

Actuals = 

VAR TimeSelected = [ViewSelected]
VAR ActualAmt = CALCULATE(SUM(Summary[Value]),Summary[Version]="Actual")
VAR MonthlyAct =ActualAmt
VAR YTDAct = CALCULATE(SUM(Summary[Value]),Summary[Version]="Actual",DATESYTD(Calender[Date]))
VAR QTDAct = CALCULATE(SUM(Summary[Value]),Summary[Version]="Actual",DATESQTD(Calender[Date]))
VAR RESULT = FORMAT(IF(HASONEVALUE('Time'[View]),SWITCH([ViewSelected],1,MonthlyAct,2,YTDAct,3,QTDAct),MonthlyAct),"$0,0;($0,0)")

RETURN IF(ISFILTERED('Chart of Accounts'[Account]),RESULT,SWITCH(LASTNONBLANK(Header[Header Description],1),"     GM% Net Sales",ROUND([Gross Margin Actual]*100,1)&"%","      Opex % Net Sales",ROUND([Exp % Actual]*100,1)&"%","     EBITDA % Net Sales",FORMAT([EBITDA Actual],"0%;(0%)"),RESULT))

Model DiagramQuery Runtime

Income StatementBreakup Page

[TeigeGao]

Hi @Krishna_Mysore ,

Actaully, there is no much space to optmisize the DAX query for the measure, the measure will take much time because it will take much time to handle the large amount of data, besides, the calculate on the data source is also a realtime.

Considering that the data you need is the result of SUM(), we can prepare another table on the data source, we can calculate the data on the data source rather than importing data to PowerBI and calculating them on PowerBI.

Best Regards,

Teige

[Krishna_Mysore]

Hi @TeigeGao 

Thanks for your feedback. Do you think that the highlighted part in the measure below is slowing down at all?

Because the Summary Fact table is only about 700 thousands rows.