Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
@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
@HarrisMalik Silly me, of course this works!! thanks for your help. Your comment along with information in the link below sorted my issue!! 🙂
@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
@HarrisMalik Silly me, of course this works!! thanks for your help. Your comment along with information in the link below sorted my issue!! 🙂
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
99 | |
76 | |
64 | |
58 |
User | Count |
---|---|
148 | |
113 | |
97 | |
84 | |
67 |