Forum Discussion
Multiple relations in a circle
Hello everybody,
sorry for the title, but I don't get a better name...
I'm in trouble with the following requirement:
I have the following queries:
Sprint (Contains start date and end date and Sprint number)
SprintDates (contains for each sprint day a row) [HelpTable]
Resource (Contains resource no and sprint no)
Resource Revenue (Contains resource no, date and revenue)
Anf of course a date table...
The relationship is between Sprint and Sprintdates; Sprintdates and date tabel; Sprint and resource
NOW i wan't to relate the resource revenue to this model to show the Revenue by Sprint.
Acutally there is no relation between resource revenue and sprint. From my point of view it is a combination of date and resource no. Seems to be something like this:
Of course this is not possbile, but I didn't get a solution to solve this issue...
I think about filtering the revenue table by the dates AND the assigned resources to the sprint..
Any comments or ideas?
Thanks in advanced,
Tristan
- Hey Matt, I have now your book next to me.. :)
I now do a redesign of the model and create also a resource look up table which connects between the resource line and resource revenue table... And now it works.
I will share my file with your, when I'm done with the rest..
5 Replies
You should think about your tables as being data tables and lookup tables. Here are a couple of articles I wrote on the topic
http://exceleratorbi.com.au/the-optimal-shape-for-power-pivot-data/
http://exceleratorbi.com.au/create-lookup-table-power-pivot/
it is not clear to me which of your tables are/should be lookup tables and which are/should be data tables, or even if you should be combining your tables in a different way.
I'm thinking get rid of your sprintdate table and replace it with a calendar table
calendar joins to revenue
sprint joins to resource joins to revenue.
i would lime to see a sample workbook to be able to be more specific.
Hello Matt,
thanks for your comment.
I sleep a night over this issue, I I think, the problem is the following:
The ResourceRevenue query is the data table and the resource and the date is the lookup table.
And you will get the right result, when you filter manually a specific timeframe and a specific group of employees.
But I don't want to to this manually of course. :) I just want to set one checkmark for a specific "Sprint Index", which defines the above mentoined timeframe (Start- and enddate) and the relation to the group of resource.
Is this kind of "thinking" possible?
Tristan
TristanKuesters wrote:
and the resource and the date is the lookup table.I'm not sure that is correct (it could be, not sure). Does this table have a primary key? ie one column that uniquly identifies each row in the table that can be joined to your data table?
