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

Best Way to Filter Dimension Table to Only Values Used in Fact Tables

Hello,

 

I have a fact table that refers to about 30 of a dimension table's 100 keys. What is best practice to only load dimension table keys that are in the fact table?

 

I can get what I want by

* referencing the (VERY LARGE) fact table

* removing all columns but the foregin key column

* removing duplicates

* merging (inner join) with the dimension table

 

I was thinking there has to be a less expensive way than this though. It would be nice to not have all the unnecessary dimension table data. 

Thanks!

1 ACCEPTED SOLUTION
lbendlin
Super User
Super User

Use Power Query and Query folding to pre-filter your data, or do that in your data source.

 

NOTE:  yes, you should reduce the amount of data you load into Power BI as early as possible, but not earlier. Take some time to consider if you are destroying information by applying this filter.

View solution in original post

2 REPLIES 2
Eyelyn9
Community Support
Community Support

Hi @rlussky ,

 

Could you tell me if @lbendlin 's post helps you a little? If it is, kindly Accept it as the solution to make the thread closed. More people will benefit from it.Hope to hear from you‌‌😀

 

Best Regards,
Eyelyn Qin

lbendlin
Super User
Super User

Use Power Query and Query folding to pre-filter your data, or do that in your data source.

 

NOTE:  yes, you should reduce the amount of data you load into Power BI as early as possible, but not earlier. Take some time to consider if you are destroying information by applying this filter.

View solution in original post

Helpful resources

Announcements
Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

User Group Leader Meeting January 768x460.png

Calling all User Group Leaders!

Don't miss the User Group Leader meetings on January, 24th & 25th, 2022.

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors