cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Tridiv
Frequent Visitor

new table with value from different table

Here is what I need to do, any help is appreciated

 

I have 2 tables 

 

Table 1. Budget Table that is limited to department level - 4000 rows

Table 2. An Actual table that goes all the way down to line level 350,000 rows

 

I can't create a relationship since it gives me an error "missing intermediate data to connect two columns"

 

I would like to either aggregate my line level table so that it sums up to department level and join the Budget Table using merged queries, the problem is any join I use takes ages and I dont really have the patience to wait and see if that works especially if the wait time is a recurring thing, am i doing something wrong here?

 

The other thing I was thinking about was creaating a new table (table 3) that aggregates Table 2 at department level and I create a relationship or a merged query between Table 1 and Table 3 - Any suggestions on how do I create that third table? I would I have thought it to fairly simple to create a pivot table using pivot columns in a new table. Just can't seem to get my head around it. 

 

Thanks

2 ACCEPTED SOLUTIONS
HarrisMalik
Continued Contributor
Continued Contributor

@Tridiv It is possible to make relationship between two fact tables (in your case budget and actuals) on different grain levels. All you need to do is to join them to conformed dimensions like Time and department in your case. 

 

The dimension tables have one record for each of your business key so you can make one to many relationship between your fact table and dimension e.g.

 

Actual - Department - Budget

Many  -      One         - Many

 

Using this model you can do all of your analysis on department level using Budget and actuals.

I suggest to read about dimensional modelling which will help you to create data models in Power BI.

 

Harris

View solution in original post

@HarrisMalik Silly me, of course this works!! thanks for your help. Your comment along with information in the link below sorted my issue!! 🙂 

 

https://community.powerbi.com/t5/Desktop/Error-this-could-be-because-there-is-missing-intermediate-d...

View solution in original post

2 REPLIES 2
HarrisMalik
Continued Contributor
Continued Contributor

@Tridiv It is possible to make relationship between two fact tables (in your case budget and actuals) on different grain levels. All you need to do is to join them to conformed dimensions like Time and department in your case. 

 

The dimension tables have one record for each of your business key so you can make one to many relationship between your fact table and dimension e.g.

 

Actual - Department - Budget

Many  -      One         - Many

 

Using this model you can do all of your analysis on department level using Budget and actuals.

I suggest to read about dimensional modelling which will help you to create data models in Power BI.

 

Harris

View solution in original post

@HarrisMalik Silly me, of course this works!! thanks for your help. Your comment along with information in the link below sorted my issue!! 🙂 

 

https://community.powerbi.com/t5/Desktop/Error-this-could-be-because-there-is-missing-intermediate-d...

View solution in original post

Helpful resources

Announcements
MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Top Solution Authors
Top Kudoed Authors