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.
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.
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.
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.
Hi,
I get an access denied message.