How to filter amongst two tables from SQL?

[Tooshay]

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.

[v-yulgu-msft · Accepted 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

[Anonymous]

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