Hello! I have a data modeling & measure building roadblock that I can’t seem to wrap my head around.
My data model consists of the following,
Fact Table #1 - Unavailable at Cut Off
Records items that were deemed “Not Available” at the daily cut off time
If item is available it is not recorded for that day
Columns: Record Date, Product ID
Fact Table #2 - Sales History
Historical look at product quantity sold on a daily basis
If item was not sold it is not recoded for that day
Columns: Sale Date, Product ID, Quantity
My goal is to show a list of items for a selected day that were out of stock. If the item is listed as not available, I want to show the sum of the sale quantity over the last 7 days relative to the unavailable record date.
No matter what I try in my model and calculate on measures, the results are wonky. I believe the core issue is I’m trying to use a fact table as a dimension table for another fact table.
I just can’t figure out how best to normalize this.
Have you checked out the workouts section under the data Modelling section.
If might help you get you started.
Have you checked out the Courses: Data Transformations And Modeling.?
Fact tables and Dimension tables are two different things.
Dimension table are one item example date table, product table, Dimenstion table are one to many relationship. Many relationship is mostly related to the fact table where you can have multiple items for the same thing.
I would also recommend that you go through some of the courses with EDNA portal. Some of the courses you will need to be paid member
any chance you can upload a version of your file that is ok to share (in terms of data privacy)? hard to tell what you are trying to do without seeing your model and measures. It sounds like you need a dimension table, that can be easily generated with the SUMMARIZE() or VALUES() function. It looks like here you need to enter a new table with DAX using VALUES(‘sales history’[product id]) but you’ll also need a date table, you can enter one into power query using this query described here:
After you have both of those you need to create one to many relationships between both of your sales tables to the date table and newly made product table, learn about that here https://www.youtube.com/watch?v=xGFbLvs31Io&t=1s
but it’d probably be good if you do the whole beginners guide to power bi on youtube that I linked above, it’s a great series, I did it myself 3 years ago!