Relationship between different worksheets

[Anonymous]

Dear Community,

I hope all of you are ready for the weekend! 😉 

Yet before the fun starts I have one question, which I have not managed to solve myself. More concretely, I have 8 worksheets, which I would like to analyse w/ the Power Pivot and then uplaod to the PowerBI. The relationship between the worksheets is as follows:

-4 divisions, with 2 worksheets (Original & Update financial plan) per division;

-Every worksheet contains IMO column, which essentially links the four divisions (e.g. XYZ number in all 4 divisions and 8 worksheets).

I successfully set up everything for the first division and the measures seemed to function as well. Yet, when I tried to add the three remaining divisions, Excel did not recognise the relationship. Hence the question, how to set up a relationship between 4 divisions with interlinked elements (IMO number)?

Attached, you can find two snips summarizing the status and the logic of my model.

Best,

Karolainis 

Relationship view 

Power Pivot 

[v-juanli-msft]

Hi @Anonymous 

Agreed with mahoneypat, after appending and transformations in Power query, then you can get a big fact table with many dimensions.

Then you can create many dimension tables and create relationships as follows:

Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[mahoneypat]

A few suggestions

1. You definitely should append all the tables with the same type of data (i.e., all 4 original and all 4 update tables).  You should be able to use the "Combine & Edit" feature to do it in one shot for each (original and update).

2. Depending how similar the original and update tables are, you should consider either merging or appending those two tables too (your model should have only 1 or 2 tables (2 or 3 when you add a Date table)

3. FYI that you can disable load on all the appended queries by right clicking on them and unchecking Enable Load.

4. I suggest you build directly in Power BI w/o doing PowerPivot first.  You eliminate a step and IMO Power BI is a better build experience (query is the same of course).

Doing the above will result in a much simpler model and make your analysis/visualization much easier.  Simple model, simple DAX.

Regards,

Pat