filter multiple facts table without combing them by using sql statement

[Anonymous]

I have 3 tables and i want it to be filtered before importing to powerbi desktop.

I used a SQL query by adding this below:

SELECT a.*,b.*, c.*
FROM test.fact_a a

inner join test.concattable t on e.id_pk = t.id_fk
inner join fact_b b on t.id_fk = b.bid_pk

where e.date_id = 1

This SQL query did the work but it combined all the 3 tables into one table which is not what i wanted. The table A and C is a fact tables. Table B is a concat that take the FK from A and C . I like to keep them separate in the data model .

this is suppose to be a star schema and 3 of these tables are 40 million rows of data each so i can't really import all 3 together and have to filtered down. 

[Anonymous · Accepted solution]

Hi @Anonymous ,

When you use the above query to get the data from the SQL data source, you end up with only one "composite" table in the model (containing data from tables a, t and b). If the join mode of the data source is import, you can try to break this "composite" table into multiple tables by referring to the following link.

How To Break Out Large Tables Into Multiple Tables And Build A Model - Power BI Tips

Alternatively, you can write a SQL statement like the one below for table a and table b respectively. Table t can be joined directly because it is a concat table...

Get fact table a

Get fact table b

Best Regards