How to improve my report performance further

[MasonMA]

Hi folks,

Lately I’ve been struggling with some performance issues in one of my reports and I’d like to hear how other users have approached similar situations.

Here’s the setup:

1.The report is built on DirectQuery to several Power BI semantic models. I switched it to composite mode for adding custom columns later. 

2.The source models come from another data team and are all based on SAP BW (attached picture shows how PQ expanded these BW report data. )

3.Due to data policy I don’t have the option to import; DirectQuery is my only choice.

At first, I created a number of calculated columns in this composite mode (business requirements made them necessary at the time). Performance was poor, sometimes close to 1 minute just for the first load. Subsequent interactions got faster due to caching, but still not great.

Recently, I worked with the model owners to shift those calculated columns into Power Query. That helped, not dramatically as i expected. With DAX Studio, I still see query times in the range of 5–40 seconds depending on the scenario.

Right now the report is relatively simple:

• Just a few measures (basic SUM aggregation and a conditional SUM).

• No complex DAX logic.

The Measure i used with some logic

TransactionAmount(LC) =
VAR MultiCurrencyCheck = COUNTROWS(VALUES('Expenditure Dataset- FAS'[Currency - Key]))
VAR result=
    IF( MultiCurrencyCheck=1,
        SUM('Expenditure Dataset- FAS'[Transaction Amount (LC)]),
        BLANK()
        )
RETURN
result

If i use this UI generated query and run it in DAX studio

DEFINE
	VAR __DS0FilterTable = 
		TREATAS({"2025-26"}, '2. Fiscal Year'[FiscalYearNew])

	VAR __DS0FilterTable2 = 
		FILTER(
			KEEPFILTERS(VALUES('Expenditure Dataset- FAS'[Fund Group - Name])),
			NOT(
				'Expenditure Dataset- FAS'[Fund Group - Name] IN {"Assessed Contributions",
					"Non-Assessed Contributions",
					"Not assigned",
					"Net Voted Revenue"}
			)
		)

	VAR __DS0Core = 
		SUMMARIZECOLUMNS(
			ROLLUPADDISSUBTOTAL(
				ROLLUPGROUP(
					'Expenditure Dataset- FAS'[Funds Center - Key],
					'Expenditure Dataset- FAS'[Cost Center - Key],
					'Expenditure Dataset- FAS'[Fund - Key],
					'Expenditure Dataset- FAS'[Commitment Item],
					'Expenditure Dataset- FAS'[G/L],
					'Expenditure Dataset- FAS'[Document type - Name],
					'Expenditure Dataset- FAS'[Predecessor Doc. - Key],
					'Expenditure Dataset- FAS'[Item Des (Customized)],
					'Expenditure Dataset- FAS'[Vendor (Customized)],
					'Expenditure Dataset- FAS'[Currency - Key]
				), "IsGrandTotalRowTotal"
			),
			__DS0FilterTable,
			__DS0FilterTable2,
			"TransactionAmount_LC__EPage", '#Measure- FAS'[TransactionAmount(LC)_EPage],
			"TransactionAmount_CAD__EPage", '#Measure- FAS'[TransactionAmount(CAD)_EPage]
		)

	VAR __DS0PrimaryWindowed = 
		TOPN(
			502,
			__DS0Core,
			[IsGrandTotalRowTotal],
			0,
			'Expenditure Dataset- FAS'[Predecessor Doc. - Key],
			1,
			'Expenditure Dataset- FAS'[Funds Center - Key],
			1,
			'Expenditure Dataset- FAS'[Cost Center - Key],
			1,
			'Expenditure Dataset- FAS'[Fund - Key],
			1,
			'Expenditure Dataset- FAS'[Commitment Item],
			1,
			'Expenditure Dataset- FAS'[G/L],
			1,
			'Expenditure Dataset- FAS'[Document type - Name],
			1,
			'Expenditure Dataset- FAS'[Item Des (Customized)],
			1,
			'Expenditure Dataset- FAS'[Vendor (Customized)],
			1,
			'Expenditure Dataset- FAS'[Currency - Key],
			1
		)

EVALUATE
	__DS0PrimaryWindowed

ORDER BY
	[IsGrandTotalRowTotal] DESC,
	'Expenditure Dataset- FAS'[Predecessor Doc. - Key],
	'Expenditure Dataset- FAS'[Funds Center - Key],
	'Expenditure Dataset- FAS'[Cost Center - Key],
	'Expenditure Dataset- FAS'[Fund - Key],
	'Expenditure Dataset- FAS'[Commitment Item],
	'Expenditure Dataset- FAS'[G/L],
	'Expenditure Dataset- FAS'[Document type - Name],
	'Expenditure Dataset- FAS'[Item Des (Customized)],
	'Expenditure Dataset- FAS'[Vendor (Customized)],
	'Expenditure Dataset- FAS'[Currency - Key]

Any suggestion is appreciated! 

[sevenhills]

Please see if it helps

https://techcommunity.microsoft.com/blog/sapapplications/improve-sap-bw-performance-by-applying-the-...

[v-menakakota]

Hi @MasonMA ,
Thanks for reaching out to the Microsoft fabric community forum. 

I would also take a moment to thank  @lbendlin  , for actively participating in the community forum and for the solutions you’ve been sharing in the community forum. Your contributions make a real difference.

I hope the above details help you fix the issue. If you still have any questions or need more help, feel free to reach out. We’re always here to support you 

Best Regards, 
Community Support Team.

[v-bmanikante]

Hello @MasonMA ,

I am also part of CST Team and we’d like to confirm whether your issue has been successfully resolved. If you still have any questions or need further assistance, please don’t hesitate to reach out. We’re more than happy to continue supporting you.

Regards,

B Manikanteswara Reddy

[v-menakakota]

Hi @MasonMA ,
Thanks for reaching out to the Microsoft fabric community forum. 

I hope the above details help you fix the issue. If you still have any questions or need more help, feel free to reach out. We’re always here to support you 

Best Regards, 
Community Support Team.

[lbendlin]

First rule of Direct Query is to keep the number of Power Query transforms to an absolute minimum.  You have way too many. Check if your query folds, and try to push all these additional columns up into the SAP BW.