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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.