Forum Discussion
Dymanic data source
Hi,
The forecast table (Table B) should have 2 date columns - one which tells us the month for which the forecast is and another which tells us the Month in which the forecast was prepared. Using the USERELATIONSHIP() function, we should be able to get what you want.
"a bit clumsy" is a nice way to put it. I think it is irresponsible for your business requestors to ask you to create such a challenging report with the current experience. Try to work on the issue piece by piece. Starting with the 23 tables will get you nowhere. Start with Ashish_Mathur 's suggestion.
"bridging tables" - that is not recommended. Use proper dimensions to control these unrelated fact tables.
The ask should be doable with the right data model. What data model do you have so far?
Hi Ibendlin,
Thank you so much for your help.
I am a beginner user of Power BI and have been using the tool for 4 months so I have to first admit the design / data model is a bit clumsy.
My data model pulls from 23 tables (all in Excel format) from Share Point (all from different sources in my organisation) and most of these tables have data from 1 to 24 months.
Relationships are built based mainly on the month; location ID; and staff ID parameters which are commonly found in these tables. However they are often spelt/ formatted differently in different tables / sources.
Currently the model look like this (sorry for blurry image):
I hope that I am headind to the right direction by describing the data model this way.
However any advice / feedback is much appreciated since it will be a great learning experience for me.
Thank you. Have a good day.
Kind regards,
Janis
"a bit clumsy" is a nice way to put it. I think it is irresponsible for your business requestors to ask you to create such a challenging report with the current experience. Try to work on the issue piece by piece. Starting with the 23 tables will get you nowhere. Start with Ashish_Mathur 's suggestion.
Sorry also want to mention that the challenges (as a beginner) I have is that I cannot reduce the number of tables pulled to my Power BI report since the requirement for my internal reporting is to provide a comprehensive Power BI report to analyze all these 23 tables (with data like actual profits; forecast profit; budget profit; various operation KPI; assets details; payroll details, etc across 12-24 months) in one report. Therefore the data model looks quite overwhelming at the moment. Thank you again for your time in helping.