topic Re: DAX Query Performance in source dataset vs report in DAX Commands and Tips

DAX Query Performance in source dataset vs report

lemaribdb — Mon, 22 Jul 2024 18:18:16 GMT

Dear PBI community,

I have an issue with load times.
I have source dataset with report and a report that uses live connection to this datset/semantic model.
Orignally, I had my measure in the undeerlying report that uses the live connection, the query timing was pretty good, no noticable load time.
But when I moved my measure to the source dataset, the timing in the underlying report is more than horrible. From 1 second (and it still is 1 second when using the exactly same page/visualizations in source dataset) to 20 second, making the report unusable.
Is that something common I was unaware of?
When I take the query and run it in Dax Studio, it's as fast (or faster) as in when ran in the source dataset.
I thought my DAX is getting to complex but since it runs pretty fast when in source model/report (and was pretty fast when the measure was in underlying report), I feel a bit lost.

Re: DAX Query Performance in source dataset vs report

Anonymous — Tue, 23 Jul 2024 02:57:50 GMT

Hi lemaribdb ,

Regarding your question, is the connection mode of the report 'Direct Query'?

In 'Direct Query' mode, data query and calculation are completed in the data source, and the speed depends on the performance of the database. You can try to reduce the amount of single query or use a composite model to improve performance.

Use composite models in Power BI Desktop - Power BI | Microsoft Learn

Best Regards,
Wenbin Zhou

Re: DAX Query Performance in source dataset vs report

lemaribdb — Tue, 23 Jul 2024 07:00:30 GMT

Hi, sadly, as I mentioned in my initial text, I'm using live connection mode 😞

Re: DAX Query Performance in source dataset vs report

lemaribdb — Tue, 23 Jul 2024 07:03:57 GMT

I think I've found the solution, it was more obvious then I thought.
The queries were fast using dax studio or measures directly in my source model. But not in the underlying report.
The measures in source mode or dax studio are made to localhost data, where the underlying report connect to server nontheless. It was just the company server acting up on this day, it works normally today.