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

Prepping for a Fabric certification exam? Join us for a live prep session with exam experts to learn how to pass the exam. Register now.

Reply
akhaliq7
Post Prodigy
Post Prodigy

Creating numerical columns using a group by add to fact table or create as a dim table

i have been working in power bi for a while and got into the habit of keeping numerical information that is created using a group by seperate from my fact table and creating a relationship between both tables in pbi desktop. But as I have been gaining experience starting to realise this may not be the best solution instead it is best to do joins to the fact tables (using sql) and bring them in as one fact table. Dimensions should be kept for anything that can be called a category or a date table.

 

Can someone explain further 

1 ACCEPTED SOLUTION
lbendlin
Super User
Super User

It depends on the nature of your facts. For example if you have Orders and Shipments then you could theoretically keep them in the same fact table.   But if you have Sales and Quota then you cannot combine these facts, they have to stay separate.  And there should be NO link between the fact tables !!!

 

Another thing to consider is the size and growth characteristics of your fact tables. Some will be slow growing, others will keep growing massively every day and you will want to think about  partitions and aggregations etc.

 

Having numerical columns to link between facts and dimensions is considered good practice as it helps to improve the join performance.

View solution in original post

2 REPLIES 2
lbendlin
Super User
Super User

Think about the nature of your data. Dimensions are something you filter by. Facts are something you count/sum up.

 

Don't join tables just because you can.  Your data model needs to reflect the business problem you are trying to solve for.

lbendlin
Super User
Super User

It depends on the nature of your facts. For example if you have Orders and Shipments then you could theoretically keep them in the same fact table.   But if you have Sales and Quota then you cannot combine these facts, they have to stay separate.  And there should be NO link between the fact tables !!!

 

Another thing to consider is the size and growth characteristics of your fact tables. Some will be slow growing, others will keep growing massively every day and you will want to think about  partitions and aggregations etc.

 

Having numerical columns to link between facts and dimensions is considered good practice as it helps to improve the join performance.

Helpful resources

Announcements
May PBI 25 Carousel

Power BI Monthly Update - May 2025

Check out the May 2025 Power BI update to learn about new features.

May 2025 Monthly Update

Fabric Community Update - May 2025

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