Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi,
I have the folowing situation
Table 1= Orders (400.000 rows)
Table 2 = Orderlines (1.500.00 rows)
Step 1. Get the data into PowerBI
Step 2. Filter table 1 (orders) for only year 2017
Step 3. Merge this 2 tables on the field orderID
Result:
With the merge table i have a dataset with orders and orderlines that i need filtered on year 2017.
Table 1 (orders) is reduced with the date field, so this not too big
But table 2 (orderlines) loads all the original data so the PowerBI file is verry large.
How can i reduce my PowerBI file or table orderlines?
With kind regard
Norbertus
Solved! Go to Solution.
For now i made for all the tables i need in the Dashboard a SQL-View. So i can easily handle the input and the performance of the database / server is.
With the join in the "advanced options" the refresh time was verry long.
For now i made for all the tables i need in the Dashboard a SQL-View. So i can easily handle the input and the performance of the database / server is.
With the join in the "advanced options" the refresh time was verry long.
Interesting, I would think the performance should be similar. There are definitely benefits to having a view, but essentially you are still executing the same query when you select data from a view. But if this approach gives you shorter refresh times, then you are good to go. Be sure to mark this thread as resolved.
hi @Anonymous,
What is the origin of the base?
está usando uma consulta SQL?.
Hi,
yes, the data is stored in a SQL database.
I had thougt to make first for each table a view with the nessesarry data. Then is it also not required to make a mergetable in PowerBI because I can join the tables in the SQL View.
Where is the split to do the transaction in SQL or in PowerQuery?
When you get the data you can put a SQL command instead of selecting the table in the structure. In this command you can add the filter to 2017 in the WHERE clause if this is the case:
Hi,
Is it also possible to place in this "advanced option" a SQL statement with a join to my orders table so i can filter on the field date?
My table "orderlines" has to be reduced with the filter year in the table "orders"
You can do this through SQL if your goal is to reduce the amount of data to make the file lighter. If you do this inside Power BI, it will work but it will not reduce the file size. Your SQL will look something like this:
SELECT * FROM table2 WHERE table2.Year = (select DISTINCT Year FROM table1)
In Table 2 i don't have a date field.
I have to reduce my PowerBI size and also dateset.
Whats the way to go?
@Anonymous If the solution Indicated by @Anonymous is not enough, put at least the table headers so we can help you better.
All you need is a join statement that will pre filter both tables, combine them and load them into Power BI. Example:
Select * from
orders
join orderlines on orders.orderid = orderlines.orderid
where orders.orderdate between '01/01/2017' and '12/31/2017'
You can replace * with specific columns that you care about. Hope this helps.