Forum Discussion
Resultset query Exceeded 1m Rows on measures unrelated direct query table.
I add new data sources all the time in tabular editor. (I use the paid 3 version but I think it would work in free 2 version).
I can send more detailed instructions when I get to a computer (away camping at the moment). Roughly though...
1) Right click add table.
2) Expand the partitions tree and there will be one with same name as the table.
3) Click on it and the M expression window should open.
4) In power bi desktop add datasource as normal and do any transforms you need.
5) Copy M code over into tabular editor from advanced editor.
6) Right click the table and click "update table schema".
6) Save back to service (I save to dev workspace and then deploy it along from there)
Super UserAnonymous , In the case of import, we configure the incremental etl and load only selective data pbix file and load the rest of the data in power bi service.
If you are premium, you can load the rest of the data in the test instance using the deployment pipeline
Premium- Deployment Pipeline, Load More Data on Test/Prod : https://youtu.be/l69cnWkoGX0
In Direct mode, you can use aggregated table to minimize the row in direct query
https://docs.microsoft.com/en-us/power-bi/desktop-aggregations
Try to simplify the expression to reduce the row queried
hi amitchandak unfortunately at the moment we are still in a development stage of our data model, where further datasources are being added to the model, which means we need a local copy of the pbix at all times. As i understand it, incremental refresh means that you cannot download the PBIX once it's loaded to the service. I don't believe there's a functionlity currently (happy to be corrected) that allows you to add further datasets to a model as you would via the power query transform functionality in desktop?
- bcdobbs
Community Champion
Are you using premium capacity or premium per user?
If so I'd look to publish my dataset once and from then on use tabular editor to develop it connected to the service.amitchandak recommendation of making use of incremental refresh is a really good one.
Another thing to consider is looking at a dual mode setup with your customer dimension running dual (both import and direct query) and your fact table staying in direct query mode. That should allow the engine to make best use of processing on the sql end.
Lastly look at building an aggregate table that also runs in dual mode.
I'd highly recommend sql bi's mastering tabular video course which covers a lot of this stuff.
Thanks Ben, I'd love to be able to use TE but what happens when i need to add additional datasources in to the power query editor? (eg views/tables). As mentioned, Incremental refresh is the ideal but currently you can't download the PBIX when incremental refresh is set up (and if i need to add a datasource in, i need a local PBIX) UNLESS there's a way to do it in the service or in TE. I've not found a way... yet! 😄 happy to hear any suggestions. I have my Sales Fact on Import as it's currently 56m rows and the reports using it have many measures that make it super slow to load on direct query. Our customer table is also 30m rows (i've cut down to the bare minimum columns but we have a large customer base) and so i've had to also switch that back on to dual mode as when it was on direct q, that's when i was getting the initial error. Unfortunately, its the curse of the BI developer when your customer requires lots of KPI metrics to report on 😞