Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Reuben
Helper III
Helper III

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:

Reuben_0-1636539567286.pngReuben_1-1636539609222.png

 

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

1 ACCEPTED SOLUTION

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





Did I answer your question? Mark my post as a solution! / Did it help? Give some Kudos!

Proud to be a Super User!




View solution in original post

6 REPLIES 6
Reuben
Helper III
Helper III

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

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_0-1636630566527.png

 





Did I answer your question? Mark my post as a solution! / Did it help? Give some Kudos!

Proud to be a Super User!




StefanoGrimaldi
Resident Rockstar
Resident Rockstar

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





Did I answer your question? Mark my post as a solution! / Did it help? Give some Kudos!

Proud to be a Super User!




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

Reuben_0-1636542295299.png

 

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 ? 





Did I answer your question? Mark my post as a solution! / Did it help? Give some Kudos!

Proud to be a Super User!




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





Did I answer your question? Mark my post as a solution! / Did it help? Give some Kudos!

Proud to be a Super User!




Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

Top Solution Authors