Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Dymanic data source

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  ...
Need Help
  • Ashish_Mathur's avatar
    Ashish_Mathur
    4 years ago

    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's avatar
    lbendlin
    4 years ago

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

  • Ashish_Mathur's avatar
    Ashish_Mathur
    4 years ago

    Hi,

    You may download my PBI file from here.

    Hope this helps.

Anonymous's avatar
Anonymous
Not applicable
4 years ago

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's avatar
Ashish_Mathur
Super User
4 years ago

Hi,

I get an access denied message.

    • Ashish_Mathur's avatar
      Ashish_Mathur
      Super User
      4 years ago

      Hi,

      You may download my PBI file from here.

      Hope this helps.

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        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