utilisation vs capacity

[Reuben]

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

[Reuben]

Is mandatory to have dates because it can change from one year to another

[StefanoGrimaldi]

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. 

[StefanoGrimaldi]

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

[Reuben]

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!!

[StefanoGrimaldi]

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 ? 

[StefanoGrimaldi · Accepted solution]

also you can over power query do the concatenate and remove the duplicates of the reference column in case