Dynamic calculated table

[Anonymous]

Hi All
Kindly help.We are struggling for some time.

The connection used is direct query.we have build the report with a small amount of data in the backend.we have created lot of calculated tables to get the desired results.Now we have the full data loaded in the db.The calculated table is failing with the 1m record exceeded error.
We have month filter drop down in the report.Tried the selected value fn to filter the calculated table based on the selection in the month filter so that the records will b less for a month.but seems not working.
Any other way to resolve this error.
Also the performance is very bad.taking a lot of time for the query to execute.

Kindly help.

[MattAllington · Accepted solution]

 What you describe in this post is not what I would recommend as an approach.   If you are building lots of calculated tables then this is unlikely to be the right approach 

Direct query is designed to be used in one of two use cases. Either you have a data warehouse configured with a star schema and a very large data set (difficult for import mode), or alternatively you have something similar but with a very high velocity of data changing (frequency of refresh is an issue).  If these are not your motivations, then you may be better to use import mode. Import mode is designed to allow you to load the data from your source and make changes to the table structure at the same time. Ideally I would recommend you create a star schema by writing views/queries at your data source and then loading them into Power BI Desktop.  Alternatively you could use power query to create the star schema/table structure during data load. 

 That’s my five cents worth 

[Anonymous]

Thanks Matt.
We have tried to build the tables in query editor but the performance was very poor that time.
We do have frequently changing data so import mode is not apt.
We have done the data modelling using dax.So at this point of time redoing the whole aggregations at the backend is not feasible since we are nearing the go live.
Any other alternative to resolve the 1 m error.
Also when we connect the backend tables without any calculations in powerbi the performance is very bad.Taking a lot of time for a drag and drop of fields in the visualization pane.

[MattAllington]

---

@Anonymous wrote:
So at this point of time redoing the whole aggregations at the backend is not feasible since we are nearing the go live.

---

Is going live with what you have got feasible?  If not, then you may need to rethink.  Direct query mode assumes you have a well structured star schema data model in a SQL DB with approrpiate indexes etc.  In this mode, Power BI sends the queries to the SQL DB instead of managing itself, but it needs to be configured in a way that will work.  From what I understand in your description, if you want a different outcome you will need to take a different approach.  JMO