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.