Dymanic data source

[Anonymous]

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:

• a matrix table like this
•  
• actual revenue data is from table A - it contacts 24 months of data
• forecast revenue data is from table B - it contacts 24 months of data
• other bridging tables and date table to build relationships between table A and table B
• a filter panel to let users select the month they want to review, then the matrix table will show the above for that particular month, filter like below
  
• 

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

[Ashish_Mathur · Accepted 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.

[lbendlin · Accepted solution]

"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.

[Anonymous]

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:

• I have a date table with the "Date" key
• I have a Forecast Table with "Calendar month" key and "Forecast version" key
• I build relationship between "Date" key in Date Table with "Calendar month" Key in Forecast Table (and make this relationship active)
• I build relationship between "Date" key in Date Table with "Forecast Version" Key in Forecast Table (and make this relationship inactive - since I cannot have more than 1 active relationship.
• Then when I put in the below, it does not quite work
• CALCULATE(SUM(Table B[Forecast Revenue]), USERELATIONSHIP(Calendar Month[Calendar Month], Forecast Version[Forecast Version]))

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.

[Ashish_Mathur]

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.

[Anonymous]

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. 

Forecast Revenue Measure = calculate(sum(Forecast[Forecast Revenue]),userelationship('Date Table'[Date Key],Forecast[Forecast EOM]))

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.

[Ashish_Mathur]

Hi,

I get an access denied message.

[Anonymous]

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

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[Anonymous]

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

[Ashish_Mathur]

You are welcome.  If my reply helped, please mark it as Answer.

[Anonymous]

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

[Ashish_Mathur]

You are welcome.

[Ashish_Mathur · Accepted 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.

[Anonymous]

Hi Ashish,

Thank you very much for your help.

Could I please check if I have interrupted your advice correctly?

• add secondary column in Forecast table (Table B):

• add forecast version in model and build relationship with Table B
• 
• create new meature to calculate forecast revenue:
• CALCULATE(SUM(Table B[Forecast Revenue]), USERELATIONSHIP(Calendar Month[Calendar Month], Forecast Version[Forecast Version]))

Much appreciated.

Kind regards,

Janis

[Ashish_Mathur]

Yes, i think so.

[lbendlin]

"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?

[Anonymous]

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

[lbendlin · Accepted solution]

"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.

[Anonymous]

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.