Forum Discussion
utilisation vs capacity
Hi experts,
I am building a model to monitor the daily space utilization in the warehouse: Each warehouse contains tanks where is storage liquid.
For that porpouse I have 3 tables:
- DimDate: Dates
- DailyStock: Fact table that contains the information about the utilisation per day and per tank.
- Capacity: It should be a dim table containing the information about the tanks (location, type, group and capacity) but it becames more complex because at the beginning of each fiscal year ( 1st july), the capacity changes (or at least it could change).
So, my problem is that I dont know how to deal with this many to many relationship in order to get the following desired output:
Attached you will find a simplified model in case it helps.
https://www.dropbox.com/s/m5yfxnv0plvf3f0/Utilisationrate.pbix?dl=0
Many thanks for the help
also you can over power query do the concatenate and remove the duplicates of the reference column in case
6 Replies
you dont need a many to many relantionship for what Im seeing you just need to make a relantion between capacity and daily stock , cause date and capacity are dim tables that contain support data, the dailystock would be the fact table,
link by tank id on both tables, you sould have only 1 capacity per tank I guess
Hi StefanoGrimaldi thanks for your promt reply.
I have create a custon column in both tables to get an ID concatenating Tank & Location. But despite this, I have a many to many relationship because in the Capacity table there are different dates.
thanks!!
but teorically you dont need a date column on teh capacity table you coudl delete it, or does the capacity change by date for each tank ?
you need to check the source for some reason you have 2 exact date, id tank and location with 2 diferent capacity you need it to be unique values or add a extra factor if missinf that determine with tank etc its the one to link correctly between tables.