cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
park77
Helper I
Helper I

filter multiple facts table without combing them by using sql statement

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. 

1 ACCEPTED SOLUTION
yingyinr
Community Support
Community Support

Hi @park77 ,

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 aGet fact table a

Get fact table bGet fact table b

Best Regards

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

1 REPLY 1
yingyinr
Community Support
Community Support

Hi @park77 ,

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 aGet fact table a

Get fact table bGet fact table b

Best Regards

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power BI Dev Camp Session 24 without aka link and time 768x460.jpg

Ted's Dev Camp - July 28, 2022

Watch Session 24 of Ted's Dev Camp along with past sessions!

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors
Top Kudoed Authors