Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
Hi there,
I would really appreciate some help / advice about setting up dymanic data source for a power BI report.
I have set up a Power BI report as follows:
Table B containing the forecast revenue is rolled over every month. However users wants to be able to see that paritcular version of forecast when they look at histrocial data. i.e. April 2021 version of forecast when they look at Apirl 2021, July 2021 version of forecast when they select July 2021, etc
Could I please check if it is possible at all to import different data set in Power BI dynamically?
All my dataset are currently in Share Point and import them via "web page".
Many thanks in advance.
Kind regards,
Janis
Solved! Go to Solution.
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.
Hi,
You may download my PBI file from here.
Hope this helps.
Hello Ashish, Ibendlin and everyone,
Thank you so much again for the great advice!
I went away and cleaned my data model. Now I have a very clean strucutre and removed all the unnecessary bridge tables, other tables, etc.
I then added the secondary (Forecast Version) column in table B and everything is working wonderfully.
However when I added the measure with the userelationship dax formula, I did not quite get what I want.
I did some more research online and I read somthing like I cannot have 2 active relationship pointing to the same table. Would it be the reason why it did not work?
to be specific:
Sorry I am still quite new to Power BI so the above may be a silly question.
I would really appreciate any help / advice from your experience.
Thank you so much again.
Hi,
What do you mean by "However when I added the measure with the userelationship dax formula, I did not quite get what I want."? You seem to have done everything correct. Share the download link of your file and show the expected result very clearly.
Hello Ashish,
Thank you so much again for your reply. Much appreciated!! Sorry for not explaining clearly enough earlier.
https://drive.google.com/file/d/1FL09IDlmknTAA2hgQwMRTw9LPuxM5Z0C/view?usp=sharing
Please kindly find a link of my file. Sorry it is a dummy version since I am not allowed to share the actual data. However it is the same structure as the actual data.
Overall:
I want to let users to select the Date Key, then a matrix table will show the actual and forecast revenue corresponding to the date key.
However there are multiple versions of forecast as well. So I want to show the forecast version corresponding to the date key that the a user have selected.
For example, if a user select date key "202107"
1. we will pull actual revenue from table "Actual" and filter "Actual EOM" column to "202107"
2. we will pull forecast revenue from table "Forecast" and filter "Forecast Version" column to "202107" and also "Forecast EOM" column to "202107"
I created measure "Forecast Revenue Measure" in table "Forecast" to do that.
However the formula only filters column "Forecast EOM" to "202107" and did not filter column "Forecast Version".
I would really appreciate any advice / direction. Many thanks for your help again.
Hi,
I get an access denied message.
Hi Ashish,
So sorry. Hope it is working this time.
https://drive.google.com/file/d/1FL09IDlmknTAA2hgQwMRTw9LPuxM5Z0C/view?usp=sharing
Many thanks for your help.
Kind regards,
Janis
Hi,
You may download my PBI file from here.
Hope this helps.
Hi Ashish,
I have downloaded the file. Much appreciated for all your help. It makes perfect sense and you have made it so easy to understand.
Thank you so much for your time.
Kind regards,
Janis
You are welcome. If my reply helped, please mark it as Answer.
Hi Ibenlin and Ashish,
Really appreciate your help on this and advice above. It is really helpful and give me good directions of what to do next. Hope you have a great day!
Kind regards,
Janis
You are welcome.
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.
Hi Ashish,
Thank you very much for your help.
Could I please check if I have interrupted your advice correctly?
Much appreciated.
Kind regards,
Janis
Yes, i think so.
"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.
User | Count |
---|---|
134 | |
72 | |
72 | |
58 | |
54 |
User | Count |
---|---|
194 | |
95 | |
65 | |
62 | |
53 |