cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
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

Accepted Solutions
HarrisMalik Continued Contributor
Continued Contributor

Re: new table with value from different table

@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

Tridiv
Frequent Visitor

Re: new table with value from different table

@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

Re: new table with value from different table

@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

Tridiv
Frequent Visitor

Re: new table with value from different table

@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
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Top Solution Authors
Top Kudoed Authors