Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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
v-yiruan-msft
Community Support
Community Support

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 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
v-yiruan-msft
Community Support
Community Support

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 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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.