Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hey All,
I have two tables, with a layout like below (from a SQL database)
Table A (filter applied so only some ID remains)
ID
1
3
5
6
8
Table B has characteristics for the entry in Table A, but there may be multiple characteristics for each entry from Table A
Table B
ID
1 - Characteristic A
1 - Characteristic B
1 - Characteristic C
.
.
.
8 - Characteristic A
8 - Characteristic B
.
.
.
So I got it figured out where it filters Table A to only the IDs I care about, but as you can see, table B is HUGE, some entries may have tens or hundreds of characteristics. I have almost 1 million entries in Table A so Table B is 10-100 mil rows.
I saw somewhere that Power BI manages this filtering if there is a relationship between the tables set up. However, it doesn't seem to be applying it when updating the query after I close and apply in query editor.
I have a parameter set up so that I only need to look at 2000 rows from Table A (so 20k-200k rows from table B). When I close and apply my query, it's still trying to import all 10-100 mil rows from table B. Why does it not filter before hand? Is there something I can do? I see that the relationship is established correctly in manage relationships (1:* many, cross filter etc.)
I can import just Table A, and I can see table B on the very right column, where I can click and expand that. This seems to give the result I want in terms of filtering, but I end up with just one table that looks like
Table A
1 - Information Table A - Characteristic A
1 - Information Table A - Characteristic B
1 - Information Table A - Characteristic C
1 - Information Table A - Characteristic D
If I could keep it as two separate tables that would be great.
Solved! Go to Solution.
Hi @Tooshay,
The established relationship between TableA and TableB will affect each other in Report View. In other words, such a relationship doesn't filter table records in Query Editor, and doesn't affect the loaded row numbers. Each time, all rows of table will be loaded to desktop, then, filter applies in visual level on report page.
In your scenario, if you want to filter data before loading to desktop, you could write SQL query in SQL statement.
Best regards,
Yuliana Gu
Hi @Tooshay,
The established relationship between TableA and TableB will affect each other in Report View. In other words, such a relationship doesn't filter table records in Query Editor, and doesn't affect the loaded row numbers. Each time, all rows of table will be loaded to desktop, then, filter applies in visual level on report page.
In your scenario, if you want to filter data before loading to desktop, you could write SQL query in SQL statement.
Best regards,
Yuliana Gu
Hi @Tooshay
If you maintain 2 seperate queries then Power BI brings data from both table based on filters and based on Dax usage the joining will happen in PBI only.
So considering your table size, you can Join table A with Table B in Query Editor itself , so that the join will heppen in DB itself.
you can have all requied columns in the new table, so you wont lose any data here.
Thanks
Raj
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
94 | |
82 | |
66 | |
59 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |