cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Microsoft MP_123
Microsoft

improve power bi performance

hi all,

 

in order to improve my report performance, want to ask you a few questions

i have about twenty visulas on my reports, there are measures that appears in a few visuals

my question is - how i set power bi to send only one query to the database and use it for the all visuals?

i mean , if the same measure appears in more than one visual, power bi send new query to each visual, instead of one query and use it to all visuals.

 

now, my report sends about thirty queries , and it can be really minimize

thanks!

1 ACCEPTED SOLUTION

Accepted Solutions
KHorseman Super Contributor
Super Contributor

Re: improve power bi performance

@MP_123 Other than writing your measures so that they are as efficient as possible on their own, I don't think there's a way in Power BI*. As I understand it, a measure is calculated relative to its filter context, so unless all the visuals are identical in form, content, and filters, the idea of sending only one query is kind of meaningless. Think of a measure as a template for how queries will be run, and each use of the measure on a visual as a resulting query. When you write a measure, you're effectively saying "Whenever I use this, make a query that performs this action against whatever filter criteria I use it with."

 

Say for instance that you have a measure called Total Sales. It's something simple like Total Sales = SUM(SalesTable[Amount]). You have one visual that shows Total Sales by month on a line chart with product category in the legend. If you know SQL, try writing out a query that would return an equivalent result table to that line chart. Now imagine another line chart that uses the same measure, but plots it by quarter with salesperson in the legend and the entire visual is filtered to only show product category X. You see how that would be a totally different query even though the Total Sales measure appears in both?

 

If you were using import rather than direct query, the measure would be evaluated against the report's internal data model rather than your source database, so it would perhaps gain something in performance that way. That's the trade-off between the two query methods: import keeps everything in one package so the queries don't have to run across the internet to your source database, but the data is only as up-to-date as the most recent refresh time. Direct query always has access to the realtime contents of the source, but every query has to run against that source.

 

* Note that I said "in Power BI." There may be a way in your source. Instead of querying your base tables, create views in your database that are already a close fit to the filter context you'll be using in your Power BI report. Connect to those views instead of the base tables and things could run more efficiently. Power BI will still have to query your database the same amount, but the queries themselves will be simpler.


Did I answer your question? Please mark it as a solution.

Proud to be a Datanaut!

View solution in original post

2 REPLIES 2
KHorseman Super Contributor
Super Contributor

Re: improve power bi performance

@MP_123 Other than writing your measures so that they are as efficient as possible on their own, I don't think there's a way in Power BI*. As I understand it, a measure is calculated relative to its filter context, so unless all the visuals are identical in form, content, and filters, the idea of sending only one query is kind of meaningless. Think of a measure as a template for how queries will be run, and each use of the measure on a visual as a resulting query. When you write a measure, you're effectively saying "Whenever I use this, make a query that performs this action against whatever filter criteria I use it with."

 

Say for instance that you have a measure called Total Sales. It's something simple like Total Sales = SUM(SalesTable[Amount]). You have one visual that shows Total Sales by month on a line chart with product category in the legend. If you know SQL, try writing out a query that would return an equivalent result table to that line chart. Now imagine another line chart that uses the same measure, but plots it by quarter with salesperson in the legend and the entire visual is filtered to only show product category X. You see how that would be a totally different query even though the Total Sales measure appears in both?

 

If you were using import rather than direct query, the measure would be evaluated against the report's internal data model rather than your source database, so it would perhaps gain something in performance that way. That's the trade-off between the two query methods: import keeps everything in one package so the queries don't have to run across the internet to your source database, but the data is only as up-to-date as the most recent refresh time. Direct query always has access to the realtime contents of the source, but every query has to run against that source.

 

* Note that I said "in Power BI." There may be a way in your source. Instead of querying your base tables, create views in your database that are already a close fit to the filter context you'll be using in your Power BI report. Connect to those views instead of the base tables and things could run more efficiently. Power BI will still have to query your database the same amount, but the queries themselves will be simpler.


Did I answer your question? Please mark it as a solution.

Proud to be a Datanaut!

View solution in original post

Microsoft MP_123
Microsoft

Re: improve power bi performance

@KHorsemanthanks!

Helpful resources

Announcements
New Kudos Received Badges Coming

New Kudos Received Badges Coming

Kudos to you if you earned one of these! Check your inbox for a notification.

Microsoft Implementation for Communities Wins Award

Microsoft Implementation for Communities Wins Award

Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Kudoed Authors (Last 30 Days)
Users online (1,365)